-3

Please help me to convert the following values to datetime:

DayLastAccessed
-23569
-23789
-23629
-23564
-23732
0
0

The above column is smallint type in SQL Server.

I'm using the following query to convert, but no luck:

select DayLastAccessed, cast(DayLastAccessed as datetime) from Webs

Please help me on this. Thanks in advance.

5
  • 3
    Well, (1) why is this stored as a smallint in the first place, and (2) what "date" does -23569 represent? Jan 21, 2015 at 17:26
  • 1
    Hey, aron, I tried the following query----------------------- select DATEADD(d, DayLastAccessed + 65536, CONVERT(datetime, '1/1/1899', 101)) AS lastAccessDate, DayLastAccessed from Webs ---------------- And I'm getting a proper date. So that's why I asked. Thanks for helping me on this Jan 21, 2015 at 17:29
  • 2
    Using the calculation in your previous comment it sounds like for some (very very strange) reason the date was stored as number of days prior to '2078-06-07'. At that point the following works SELECT dateadd(d, LastDateAccessed, '2078-06-07') FROM tablename Jan 21, 2015 at 17:46
  • Hey Kenneth, still getting the same output. 2078-06-07 Jan 21, 2015 at 18:19
  • 2
    I don't see how this is doable unless you tell us what the smallint represent as @AaronBertrand suggested. Jul 13, 2018 at 18:51

1 Answer 1

4

A smallint data type uses the msb (most significant bit) to indicate the sign of the value. The range of values, therefore, is -2^15 (-32,768) to 2^15-1 (32,767).

If the value is converted into an unsigned integer, these values look like the number of days since 1900-01-01 (the standard "epoch" value used by many Windows apps).

You can fairly easily convert the valid range of smallint values into dates using this:

DATEADD(DAY, 0, CONVERT(int, CONVERT(varbinary(2), CONVERT(smallint, -23732), 0), 0))

So, for the values listed in your question, you get:

SELECT DATEADD(DAY, 0, CONVERT(int, CONVERT(varbinary(2), CONVERT(smallint, -23569), 0), 0))
SELECT DATEADD(DAY, 0, CONVERT(int, CONVERT(varbinary(2), CONVERT(smallint, -23789), 0), 0))
SELECT DATEADD(DAY, 0, CONVERT(int, CONVERT(varbinary(2), CONVERT(smallint, -23629), 0), 0))
SELECT DATEADD(DAY, 0, CONVERT(int, CONVERT(varbinary(2), CONVERT(smallint, -23564), 0), 0))
SELECT DATEADD(DAY, 0, CONVERT(int, CONVERT(varbinary(2), CONVERT(smallint, -23732), 0), 0))
╔═════════════════════════╗
║ 2014-11-26 00:00:00.000 ║
║ 2014-04-20 00:00:00.000 ║
║ 2014-09-27 00:00:00.000 ║
║ 2014-12-01 00:00:00.000 ║
║ 2014-06-16 00:00:00.000 ║
╚═════════════════════════╝

Having said that, it's pretty impossible to tell if these dates are accurate for your implementation. Without some other context, it's impossible to say definitively.

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