A personal repository of random information in compensation for a fatigued biological computer
Breaded IT » MS Access / SQL Server reminders » 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))