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

Question

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'

So, `dodate` is a Julian day? Any reason why you're *storing* data in that form, rather than pre-translating it during insert/update into SQL Server's native `datetime` data types? — Damien_The_Unbeliever, Nov 04 '16 at 08:45
Shall we assume - `int 2440587 = '19700101'`? And can you give us some sample data? Especially `select max(dodate) from appointment where done=0 and del=0 and userid='ak'` — Prisoner, Nov 04 '16 at 08:51
dodate is stored as Unix Time. How can i maybe Ignore everything under date 1970.1.1. Some users have appointments registered as "0" or 1200 (which is obviously fail). — DJ_TecRoot, Nov 04 '16 at 08:56
@Alex that assumption would be wrong as `2440587 = '19700101'` is not correct. If you do this: `SELECT DATEDIFF(day,'19000101','19700101')` you get `25567` — Tanner, Nov 04 '16 at 08:57
@DavidJackowiak that UNIX TIME info would have been really useful up front with this question. — Tanner, Nov 04 '16 at 08:59
Maybe OT, but from [UNIX_TIMESTAMP in SQL Server](http://stackoverflow.com/a/8837268/1050927) and [How can I convert bigint (UNIX timestamp) to datetime in SQL Server?](http://stackoverflow.com/a/2904294/1050927), should we have `0` for `01.01.1970`? — Prisoner, Nov 04 '16 at 09:08

MtwStark · Accepted Answer · 2016-11-04T09:17:54.870

3

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'

edited Nov 04 '16 at 09:17

answered Nov 04 '16 at 08:42

MtwStark

3,866
1
18
32

If the field is stored as int (as you said in comment), he can define `2440587 = '19700101'`, then it will not have any problem for `dateadd` – Prisoner Nov 04 '16 at 08:54
@Alex you are right, it depends on how dates are stored in dodate. I thought it was the cast to int of the date aka the number of days from 1900-01-01 – MtwStark Nov 04 '16 at 08:58

score 0 · Answer 2 · answered Nov 04 '16 at 08:39

0

Maybe this will help you...

Values for datetime data types earlier than January 1, 1753 are not permitted in SQL Server. SQL Server rejects all values that do not fall within the range from 1753 to 9999.

Another SQL Server data type, smalldatetime, stores dates and times of day with less precision than datetime datatype. Valid date range for this data type is from January 1, 1900 through June 6, 2079.

Greetings,

S..

answered Nov 04 '16 at 08:39

S..

39
4

May I know what related to the question? – Prisoner Nov 04 '16 at 08:42
When u go outside these ranges then u get the datetime overflow exception. – S.. Nov 04 '16 at 08:46

score 0 · Answer 3 · edited Nov 04 '16 at 08:54

0

select dodate, header, starttime,stoptime,userid,custid,objid,infoid,aname 
from appointment 
where dodate > convert(date, '01/01/1970', 103) 
  AND dodate < convert(date, getdate(), 103) and done=0 and del=0 and userid='ak'

edited Nov 04 '16 at 08:54

Tanner

22,205
9
65
83

answered Nov 04 '16 at 08:53

tint

33
5

While this code snippet may solve the problem, it doesn't explain why or how it answers the question. Please [include an explanation for your code](//meta.stackexchange.com/q/114762/269535), as that really helps to improve the quality of your post. **Flaggers / reviewers:** [For code-only answers such as this one, downvote, don't delete!](//meta.stackoverflow.com/a/260413/2747593) [The same goes for incorrect answers.](//meta.stackoverflow.com/q/287563/2747593) – Scott Weldon Nov 04 '16 at 18:17

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

3 Answers3