Convert 5 digit number to date

Question

I apologize in advance because I am sure this question has been asked before, but I don't know the name of the type of date I am working with.

How do I convert a date in this 5-digit form (e.g. 41867) into a regular yyyy-dd-mm in SQL Server?

Answer 1

SQL Server stores dates as the number of days since 1/1/1900 with the fraction as the portion of a day.

declare @d1 datetime

set @d1 = 41867

select @d1

select CONVERT(varchar(20),@d1,120)

or if you want to do it in one statement:

select CONVERT(varchar(25),cast(41867 as datetime),120)

Answer 2

This 5 digit format is called msdate and it starts from 1 Jan 1900. Easiest way to convert it into standard date is,

select convert(datetime, 44321) - 2