Search code examples
sqlsql-serverclarity

Retrieving date in numeric form (5 digits); how to convert to regular date format?

I have two dates that are numeric:
A. 65495 and
B. 65496

The dates are supposed to be: A. 04/26/2020 and
B. 04/27/2020

I have tried this code: 

select convert(varchar(25), cast(cast(tbl.mydate as integer) as datetime), 120) [newdate]
from tbl
;

But I end up getting these dates:
A. 2079-04-27 00:00:00 and
B. 2079-04-28 00:00:00

I am using clarity database on SQL-Server.

Solution

  • You can use this code:

    select dateadd(day, <your value>, dateadd(day, -65495, '2020-04-26'))

    This calculates the base date on the fly.

    Or, you can be explicit:

    select dateadd(day, <your value>, '1840-12-31')

    That is an unusual epoch date.