November 29, 2012 at 1:35 pm
I have a table with date in unix timestamp and iam using the following query to get the dates which are older than 10 mins but its giving me error :
Select class, workstation from ALERTS where active='Y' and datediff(minute, created, getdate()) > 10;
Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type datetime.
November 29, 2012 at 1:42 pm
sahmad43 (11/29/2012)
I have a table with date in unix timestamp and iam using the following query to get the dates which are older than 10 mins but its giving me error :Select class, workstation from ALERTS where active='Y' and datediff(minute, created, getdate()) > 10;
Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type datetime.
Ny UNIX timestamp I assume you mean epochdatetime? Meaning you have an int column? If you try to directly cast an epochtime to a sql datetime it will fail. The conversion will try to add the number of days to 1/1/1900 which will not work. I would highly recommend you use a datetime column and convert epoch and store it that way.
What you need to do is look up the epoch to datetime conversion. A quick google search will find several examples for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 29, 2012 at 1:45 pm
yes i have int form numbers as date like follows :
1346447979
1346447493
November 29, 2012 at 1:49 pm
What you need to do is look up the epoch to datetime conversion. A quick google search will find several examples for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 29, 2012 at 1:52 pm
I found my snippet that does this.
SELECT DATEADD(s, @EpochValue, '1970-01-01 00:00:00')
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 29, 2012 at 1:58 pm
but how do i now find out the dates that are older than 10 mins ? dont i have to use datediff ?
November 29, 2012 at 2:29 pm
sahmad43 (11/29/2012)
but how do i now find out the dates that are older than 10 mins ? dont i have to use datediff ?
select datediff(minute, dateadd(s, created, '19700101'), getdate())
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply