casting INT to Datetime date is 2 days off

Asked Dec 08 '21 at 20:38

Active Dec 08 '21 at 20:38

Viewed 366 times

So I am parsing out a large text field with several dates in it. the date format comes out like "44445" which should be "9/6/2021" but when I convert to datetime in Microsoft SQL Server 2019 i get "9/8/2021" I found that every date that I convert is 2 days off. I can of course just do a -2 before converting to make sure I get the right date but I found this really odd and wondered if anyone knew why this was happening and if I am doing something wrong

cast(44445 as datetime) result 2021-09-08 00:00:00.000

if I put the same date in excel I get "9/6/2021" which is the correct date

I found something online about excel calculating 1900 as a leap year and therefore having a different date but excel seems to be correct and SQL statement seems to be wrong so I don't know...

asked Dec 08 '21 at 20:38

Dave

2

Take a peek at https://stackoverflow.com/questions/3963617/why-is-1899-12-30-the-zero-date-in-access-sql-server-instead-of-12-31 – John Cappelletti Dec 08 '21 at 20:40
1

There is nothing inherently "more correct" in treating 44445 as 6th September rather than 8th. The two products just have a slightly different base date – Martin Smith Dec 08 '21 at 20:41
In short Select DateAdd(DAY,44445,'1899-12-30') – John Cappelletti Dec 08 '21 at 20:46
Thank you for the comments reading the provided link along with the answers given does help me understand better. – Dave Dec 08 '21 at 21:59
`44445` is not a day, it's a number. That's where you are going wrong: store values in teh correct format in the first place – Charlieface Dec 09 '21 at 01:31

casting INT to Datetime date is 2 days off

0 Answers0