[Squirrel-sql-users] incorrect date conversions (2-day offset)?

[Elliott, D. <del...@mi...>]

Hi,

I have a bunch of dates from a legacy system that are numeric(8) fields, with the dates formatted as YYYYMMDD.  They've been extracted as-is into staging tables in MS SQL Server 2012.
I went looking for the best way to convert those into MS-SQL date fields, and came up with a variety of options.
However, when I tested those options out in SQuirreL-SQL 3.9.1, they all came back 2 days off when I specified 'date' as the target data type:

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 how the convert(datetime...) call works properly compared to all the (date) options before it; and then especially the last two lines, where the dateadd expression produces the correct date, and then calling convert(date...) on that expression backs it up by two days again.

Happily for me, since I'm writing queries destined for an SSIS package), running the same query through SSMS produced the expected results.  But I can't find any options or settings in Squirrel-SQL that seem to be related to date or timezone offsets, so I'm wondering if anyone has any idea of what's going on here and how I can get SQuirreL to give me correct results?


Thanks,
Elliott
del...@mi...<mailto:del...@mi...>


________________________________

This message contains confidential information intended only for the addressee(s) named above. If you are not the addressee, or the person responsible for delivering it to the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is strictly prohibited. If you have received this message by mistake, please immediately notify the sender by replying to the message and delete the original message immediately thereafter. Thank you for your consideration and compliance with this message.