Wrong data returned when using Year function

  • Thread starter Thread starter Collosus
  • Start date Start date
C

Collosus

In Access 2003, reading from a SQL database, where dates are stored as
doubles, I wrote a query to select all records for 2008, using the function
Year(date).

The query returned all records from December 30, 2007 (39446) to December
29, 2008 (39811).

Does anyone know why I have extra records at the beginning of the year and
missing records at the end?

Thanks
 
Access's date/time implementation is as a 64 bit floating point number with
its origin at 30 December 1899 00:00:00 whereas others use 1 January 1900
00:00:00. Rumour has it that this was because the original Access
development team misunderstood the basis for determining which years are leap
years and had to adjust the origin accordingly, but I don't know how much
credence can be given to that.

Ken Sheridan
Stafford, England
 
Please post the SQL statement you are using.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
What kind of query? If it is an Access query, you should get the correct
results. If you are using a pass through query, the syntax has to be
correct for the database engine.

FYI, when asking a question about SQL, it will help you get a more specific
answer if you post the SQL statment.
 
Collosus said:
In Access 2003, reading from a SQL database, where dates are stored as
doubles, I wrote a query to select all records for 2008, using the
function
Year(date).

The query returned all records from December 30, 2007 (39446) to December
29, 2008 (39811).

Does anyone know why I have extra records at the beginning of the year and
missing records at the end?

Thanks
Click to expand...
 
Back
Top