David Jackowiak David Jackowiak - 28 days ago 6
SQL Question

SQL - datetime overflow. List all of appointments between 1970 and current day.

I have following query which would list all of users appointments between 1970 and current day but I get error that datetime coused overflow. I tried everything but I still don't know how to resolve this.

select dodate, header, starttime,stoptime,userid,custid,objid,infoid,aname from
appointment where
dodate > 2440587 AND DATEADD(d,dodate - 2440587,'1970-01-01') >= CONVERT(date,GETDATE()) and done=0 and del=0 and userid='ak'

Answer

2440587 in SQLSever is 8582-02-06, it cause the DATEADD to go to 8395 BC and it's not supported by sql server.

Since 2440587 is UNIX Timestamp, it should be in seconds from 1970-01-01 so you should simply change 2440587 in 0

select dodate, header, starttime,stoptime,userid,custid,objid,infoid,aname 
from appointment 
where 
(dodate > 0)
AND DATEADD(SECOND, dodate, '1970-01-01') <= GETDATE()
and done=0 and del=0 and userid='ak'
Comments