Sql-server – Casting int YYYYMMDD to date produces incorrect value in Squirrel-SQL

datesql serversql-server-2012type conversion

I have extracted some data from a legacy database where dates are stored as Numeric(8) values, in YYYYMMDD format. I've stored them in a staging table in that same format, and now I would like to convert them into actual date fields in SQL Server, so I went hunting for integer-to-date conversion options, and came up with a variety of things involving CAST or CONVERT.

However, when I am testing a query in Squirrel-SQL (v3.9.1, using Java 1.8.221) using those conversion options with the date datatype (which seems appropriate since I don't have time values), the dates I get back are off by two days:

select cast('20200101' as date) , 
cast(left(20200101, 8) as date) , 
convert(date, cast(20200101 as char(10)), 112) , 
convert(date, convert(char(8), 20200101)) ,
convert(datetime, convert(char(8), 20200101)) , 
dateadd(day, datediff(day,0,cast(20200101 as varchar(10))), 0) ,
convert(date,dateadd(day, datediff(day,0,cast(20200101 as varchar(10))), 0))
/* results:
2019-12-30
2019-12-30
2019-12-30
2019-12-30
2020-01-01 00:00:00.0
2020-01-01 00:00:00.0
2019-12-30
*/

Note especially those last two, where the DateAdd expression explicitly produces the correct datetime, and then using Convert on it backs it up by two days again.

When I run the same query through SSMS, it produces the expected values.

I haven't found anything anywhere that offers to explain this behavior, so I am hoping that one of you can shed some light?

One other clue that may be relevant (but which has also produced no usable google results) is that SysDateTimeOffset() is not affected, but SysUTCDateTime() appears to be off by two days as well:

select cast(sysdatetimeoffset() as varchar(40)) as Sysdtoff, sysutcdatetime() as sysUTC
/* output (actual date should be 1/22)
2020-01-22 11:45:39.9878059 -06:00
2020-01-20 17:45:39.9878059
*/

The actual time on the server is correct, and it is set to the expected time zone (-6 hours), and my examination of Squirrel-SQL's settings doesn't turn up any date-related options.

Best Answer

For integer to date conversion you may consider using the system function in msdb called dbo.agent_datetime(date int, time int). This will automatically convert to a datetime (which you could then cast as a date).

Source: https://blog.sqlauthority.com/2015/03/13/sql-server-interesting-function-agent_datetime/

Related Question