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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy