Short Date queries

  • Thread starter Thread starter Colin Basterfield
  • Start date Start date
C

Colin Basterfield

hi,

I am playing around with some manual sql queries for my app to generate
reporting information and one of the fields I am querying on is a Date field
and is in Short Date format, so one of the rows has 2/06/2004, but I can't
remember the syntax to get it out, I thought it was #2/06/2004#, but that
doesn't work, so I'm confused...

Any ideas?
TIA
Colin B
 
The display format doesn't make any difference in the query. Dates are
always stored exactly the same. You might want to suggest what you mean by
"doesn't work". You might have a time value stored in your field. What
happens if you try a criteria of
Between #2/6/2004# and #2/7/2004#
Keep in mind the above dates are in February.
 
Hi,

I just created the query using Design View and I put #02/06/2004# in the
criteria field, and then I looked at the SQL and it showed it as

SELECT TXN_DATE
FROM TXN_HISTORY
WHERE TXN_DATE=#06/02/2004#

My regional settings are set to dd/mm/yyyy and the example of Short Date
format on the table design view is the same, so how come it is swapping DD
and MM around?

Cheers
Colin B
 
Colin,

My guess is that you are trying to do a query something like:

SELECT * FROM yourTable WHERE DateField = #2/6/2004#

If you actually look at the table, and turn the formatting off, what you
will probably see is a date that also contains a time parameter, in which
case you will have to do like Duane Hookom suggests and test for a date
range rather than for a specific date. Another alternative would to write
the query like:

SELECT * FROM yourTable WHERE cdate(int(DateField)) = #2/6/2004#

Since dates are actually stored as decimal number with the decimal portion
representing the time segment, using the INT() function to strip off the
time portion of the date and then converting it back to a date would give
you the results you are looking for.

HTH
Dale
 
Literal dates in JET SQL must be in the format mm/dd/yyyy
and delimited by hashes (#) regardless of the Regional
Settings.

However, the Query Grid will use Regional Settings hence
the translation from #02/06/2004# in the Query Grid (your
Regional Settings applies) to #06/02/2004# (suitable
format for JET SQL. They both mean 02 Jun 2004.

HTH
Van T. Dinh
MVP (Access)
 
Cool, thanks...

Van T. Dinh said:
Literal dates in JET SQL must be in the format mm/dd/yyyy
and delimited by hashes (#) regardless of the Regional
Settings.

However, the Query Grid will use Regional Settings hence
the translation from #02/06/2004# in the Query Grid (your
Regional Settings applies) to #06/02/2004# (suitable
format for JET SQL. They both mean 02 Jun 2004.

HTH
Van T. Dinh
MVP (Access)
Click to expand...
 
Back
Top