Weird problem with SQL Statements with Date fields

[Anja]

Hi everyone,

I want to write a simple SQL statement that does a comparison on a date
field.

For a simple test, I have the following SQL Statement:

SELECT * FROM Records_T where TransactionDate =#12/09/2006#

I have a record that fulfills the criterion but this query returns
nothing.

I thought that something was wrong with my query but then I tried:

SELECT * FROM Records_T where TransactionDate =#25/09/2006#

But this returns the record!!!! This is bizarre, the query works for
one data set but not for the others!!

Any pointers on what is going on here? I am totally lost...do I have to
format the date string as something else?

Please help!

Cheers,
Anja

 

[Duane Hookom]

Queries must use the date format of m/d/y. According to MS Access/JET
#12/09/2006# is the 9th of December. This gets a little confusing since
there is no 25th month, Access will resolve 25/09/2006 for you to September
25th.

 

[Granny Spitz via AccessMonster.com]

SELECT * FROM Records_T where TransactionDate =#12/09/2006#

I have a record that fulfills the criterion but this query returns
nothing.

Dec. 9, 2006 is in the future. I wouldn't expect that you *already* have
transactions on that date.

Any pointers on what is going on here? I am totally lost...do I have to
format the date string as something else?

You can format the date with US date format (mm/dd/yyyy) when the month and
day are both 12 and under, but you can probably change your system settings
to match your locality. Check your Regional and Language options in the
Windows control panel.

 

[Rick Brandt]

Dec. 9, 2006 is in the future. I wouldn't expect that you *already*
have transactions on that date.


You can format the date with US date format (mm/dd/yyyy) when the
month and day are both 12 and under, but you can probably change your
system settings to match your locality. Check your Regional and
Language options in the Windows control panel.

#12/09/2006#

Will be interpretted in an Access/Jet query as December 9th 2006 regardless of
regional settings.

 

[Granny Spitz via AccessMonster.com]

#12/09/2006#

Will be interpretted in an Access/Jet query as December 9th 2006 regardless of
regional settings.

Thank you, Rick. I don't have much experience with non-US regional settings,
which is why I said "you can probably change your system settings." I would
think that Jet would accommodate non-US regional settings since Microsoft has
so many customers that aren't in the US. Jet's been around for nearly 15
years, which I would think is ample time to add this capability for loyal
customers. Guess not.

 

[Rick Brandt]

Thank you, Rick. I don't have much experience with non-US regional
settings, which is why I said "you can probably change your system
settings." I would think that Jet would accommodate non-US regional
settings since Microsoft has so many customers that aren't in the US.
Jet's been around for nearly 15 years, which I would think is ample
time to add this capability for loyal customers. Guess not.

The issue is if a date literal is hard-coded into an app then it must be treated
as the same date regardless of the machine it is running on. To do otherwise
would cause all sorts of problems. Microsoft simply chose the default treatment
to be the one they deemed would be most popular.

 

[Anja]

Thank you, Rick. I don't have much experience with non-US regional settings,
which is why I said "you can probably change your system settings." I would
think that Jet would accommodate non-US regional settings since Microsoft has
so many customers that aren't in the US. Jet's been around for nearly 15
years, which I would think is ample time to add this capability for loyal
customers. Guess not.

Thanks for the replies anyone. I would actually rather make it explicit
in my SQL statement as to how to interpret the date and ho to store
them.

Can someone post a small example as to how to set this in SQL?

Cheers,
Anja

 

[Rick Brandt]

Thanks for the replies anyone. I would actually rather make it

explicit in my SQL statement as to how to interpret the date and ho
to store them.

Can someone post a small example as to how to set this in SQL?

If you want the easiest format that is completely reliable then use the ISO
format yyyy-mm-dd. Any non-ambiguous format solves the problem, but others
attain their non-abiguity by using alpha characters for the month and that can
be a problem in other language versions.

None of this affects storage at all. Dates are stored internally as numbers.
As I post this my local date time would be stored as 38997.4810763889.

 

[RoyVidar]

Thanks for the replies anyone. I would actually rather make it
explicit in my SQL statement as to how to interpret the date and ho
to store them.

Can someone post a small example as to how to set this in SQL?

Cheers,
Anja

Have a look at

http://allenbrowne.com/ser-36.html and
http://www.mvps.org/access/datetime/date0005.htm

for some info and samples.

 

[Granny Spitz via AccessMonster.com]

The issue is if a date literal is hard-coded into an app then it must be treated
as the same date regardless of the machine it is running on. To do otherwise
would cause all sorts of problems. Microsoft simply chose the default treatment
to be the one they deemed would be most popular.

Ah, I see, much like $1.00 doesn't equal 1.00 Euros. Jet could use the
tactic of storing dates and forcing the typed in dates into US date format as
soon as the query is switched to another view (like when it optimizes SQL
statements) and just display dates according to the regional settings, but
there's *always* going to be some degree of confusion for the user when he
intends to use non-US date format and sees US date format in the query if
those month and day digits are 12 and under. ("Did I intend that date in
*my* format or in US format?") No need to open a can of worms.

 

[Granny Spitz via AccessMonster.com]

Dates are stored internally as numbers.
As I post this my local date time would be stored as 38997.4810763889.

If I may return the favor, 10/7/2006 11:32:45 AM is stored as 8 bytes in
binary little-endian format. (4 byte long data type for the number of days
since Dec. 30, 1899 and 4 byte single data type shorthand for the fraction of
a day.) I wouldn't dream of writing the binary, but the hexadecimal
equivalent is A54FFA64AF0AE340 for that date/time.

 

[Tim Ferguson]

Can someone post a small example as to how to set this in SQL?

Public Function SQLDate(SomeThing As Variant) As String

Const tfSQLDateFormat As String = "\#yyyy\-mm\-dd\#"

If IsNull(SomeThing) Then
SQLDate = "NULL"

ElseIf Not IsDate(SomeThing) Then
Err.Raise 53 ' arbitrary error number

Else
SQLDate = Format(SomeThing, tfSQLDateFormat)

End If

End Function

usage:

mySQL = mySQL & "WHERE Available = " & sqldate(dtNextAppt)


etc etc.


Tim F