David Jackowiak David Jackowiak - 1 month ago 7
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'