1

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.

  • 2
    Try starting an extended events trace and see if what is typed in SquirrelSQL is actually what is being passed to SQL Server. The fact that SSMS produces correct results indicates this is an issue with SquirrelSQL and the date type. maybe update squirrelsql and java. – Bob Klimes Jan 22 at 19:19
  • 1
    Totally agree. SquirrelSQL evidently mangles your SQL before it is being sent to SQL Server. As an aside, the old datetime types in SQL server uses 1900-01-01 as a type of int 0 "base", where many other tools uses 1899-12-31 as int 0 base date (see for instance social.msdn.microsoft.com/Forums/office/en-US/…). Fortunately, the new date types in SQL server do not allow casting between int and those datetime types, so the confusion with the base date doesn't apply for this. – Tibor Karaszi Jan 23 at 8:04
  • @TiborKaraszi: If what you are saying is true, then it's not the SQL that's mangled but rather the results, no? – Andriy M Jan 24 at 10:33
  • @Andriy, Indeed, now that I look at it closer. The only difference between the fourth and fifth value is the returned type. But having a tool that displays an incorrect representation of the value for some types are scary, close to hard to believe. There can be a combo, where the tool also mangles the input. A trace of the SQL submitted to SQL Server would say more. In any case, this tool has issues, evidently. – Tibor Karaszi Jan 25 at 13:08
1

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/

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.