View Single Post
  #6 (permalink)  
Old July 6th, 2007, 07:33 AM
robzyc robzyc is offline
Friend of Wrox
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post

quote:Originally posted by SerranoG
It's one thing to reformat to display something or to find a criteria for a string, but not for data types of DATE.

A date is a date is a date to the computer... no matter how you format it. So that's why I said that when I saw that the criteria was a date.
Not when the information being passed is a String.

Everyone (in the geeky world!) knows that dates are stored as numbers, hence the "date is a date is a date".
But SQL can only work against a text language, hence the formatting of the date is important.
The string has to be parsed into date serial by the data provider, get your month and the day around the wrong way, you've got problems...

Yes, IF the control formatting is set to the correct date format,
and the format is set correctly in the table design view, it will
work ok. But, its best practice to assume nothing with things like
this, and it has rapidly become the de facto standard to ALWAYS
work with dates in UDF format, Access and SQL Server (or most other
modern data providers) will always treat this String the same way.

I'm not trying to flame, but although what you are saying is right,
(as in dates are stored as serials), Access tries to be "smart" and
you can get some odd goings on with dates (you may not experience it
so much in the States). But, WHERE clauses are not "dates" they are
Strings, Strings must be parsed, and in order to parse correctly,
an expected format should be used.


Reply With Quote