CODEWITHSUNDEEP
ms-access
Peter
Peter

Reputation: 107

Access SQL: How to convert a year stored in an int to a date - dates are in 16th century

There are some similar questions on Stack Overflow (like questions 1923876, or question 266924 ) concerning SQL Server or TSQL. However I have received an ACCESS 2010 database and I'm trying to use the given solution with CAST etc. in ACCESS SQL, but with no success so far.

The dates are in 16 th century and there are two columns: exact_date (datetime) and estimated_year (int).

These columns should be used to sort using ORDER BY (assuming month and day as 1st January if only the year is known or estimated) the result.

Upvotes: 1

Views: 539

Answers (1)

HansUp
HansUp

Reputation: 97101

Since CAST is not supported in Access SQL, consider DateSerial(year, month, day) to convert an integer year to a Date/Time value for Jan 1 of that year.

Here is an example copied from the Access Immediate window:

estimated_year = 1505
? DateSerial(estimated_year, 1, 1)
1/1/1505

? TypeName(DateSerial(estimated_year, 1, 1))
Date

Upvotes: 1

Related Questions