datediff with unixtimestamp - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

datediff with unixtimestamp

Author

Topic

aliyesami
Starting Member

30 Posts

Posted - 2012-11-29 : 15:32:52

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.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-29 : 15:40:24

Change it to

Select class, workstation from ALERTS 
where active='Y' and datediff(minute, DATEADD(ss,created,'19700101'), getdate()) > 10;

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-29 : 15:42:22

You know a lot of weird things, Sunita.

Jim

Everyday I learn something that somebody else already knew

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-29 : 15:45:45

Not my fault Jim! I work with a few quants who use unix and perl and epoch time and everything in between. They polluted me.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-11-29 : 15:52:48

Variation that may perform better:

Select class, workstation from ALERTS 
where active='Y' and created < DATEDIFF(second, '1/1/1970', dateadd(minute, -10, getdate()))

If "created" is indexed this can do a seek instead of a scan.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources