Dates across MS Access, Excel, SQL Server

SQL Server stores dates back to 1753

 


 

Access stores the Date/Time data type as a double-precision, floating-point number up to 15 decimal places. The integer part of the double-precision number represents the date. The decimal portion represents the time.

Valid date values range from -657,434 (January 1, 100 A.D.) to 2,958,465 (December 31, 9999 A.D.). A date value of 0 represents December 30, 1899. Access stores dates before December 30, 1899 as negative numbers.

 


 

Excel can't handle negative dates (ie < 1900)

 


 

SQL convert date to string:

 ... case when Died is not null then ltrim(str(year(died))) + '-' + right('00' + ltrim(str(month(died))),2) + '-' + right('00' + ltrim(str(day(died))),2) else NULL end as DiedText,

 

 


MSaccess date snippets:

rptEndDate = DateValue("December, 31 " & Year(YearSelectionStartDate))