Date Format

[me]

Hello,

I cannot select records of a given day:

SELECT DISTINCT [X], COUNT([X]) AS Y FROM [tbl] WHERE (Format([myDateTime],
'Short Date') = ?) GROUP BY [X];

Parameter value "myDateTime": #7/10/2008#

If I use the following query with the ISO format all works well:

SELECT DISTINCT [X], COUNT([X]) AS Y FROM [tbl] WHERE (Format([myDateTime],
'Short Date') = #2008-07-10#) GROUP BY [X];

The myDateTime column has values like:

10.07.2008 20:05:16

Thank you.

 

[Michel Walsh]

If you use # you should use the US format. You just fall on July "by luck".
Take a look at

? #2008-12-31#, #2008-31-12#


and while the two 'expressions' are not the same, they both return the same
date! Thanks to OLE32AUT.dll which go to very many ways to 'make sense' of
date, but to be 'safe' about what will be returned, always use USA format
when using #. Well, there is but one exception: when you are supply a date
in the query designer... on some version, your date will be reformatted as
the setting of the preferred date format on the PC you use. To be safe, in
case of doubts, in that case, switch to SQL view.


Vanderghast, Access MVP

 

[John Spencer]

You can strip off the time portion of myDateTime using the DateValue
function.

Or In Access by using CDate(CLng(myDateTime)) - assuming the myDateTime
always has a value - nulls will generate an error.

Parameters [Enter Date] DateTime;
SELECT DISTINCT [X]
, COUNT([X]) AS Y FROM [tbl]
WHERE DateValue([myDateTime]) = [Enter Date]
GROUP BY [X];

More efficient especially if you have an index of the fiel is to use.

Parameters [Enter Date] DateTime;
SELECT DISTINCT [X]
, COUNT([X]) AS Y FROM [tbl]
WHERE [myDateTime] >= [Enter Date] AND
[myDateTime] < DateAdd("d",1,[Enter Date])
GROUP BY [X];

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================