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
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.