Date Format Conversion

[Guest]

In a query (hopefully), how do I convert a date/time value from "MM/DD/YY
HH:MM:SS AM/PM" format to just "MM/DD/YY", i.e., drop the hours, mins and
secs? Please note that in my table, months and days under 10 do not have a
leading zero. E.g., I have "6/5/04" instead of "06/05/04". So I can't just
truncate after eight characters. I'm hoping I can specify so formula or
function in the Criteria field for this value in the query. Thanks in advance.

 

[Guest]

Let's say your date field is called: dtmDate

Put this in your query: MyDateTitle: format(dtmDate, "mm/dd/yy")
other alternative: MyDateTitle:format(datevalue(dtmDate),"mm/dd/yy")
other alternative: MyDateTitle:format(datevalue(nz(dtmDate,now())),"mm/dd/yy")
Tman

 

[Rick Brandt]

In a query (hopefully), how do I convert a date/time value from
"MM/DD/YY HH:MM:SS AM/PM" format to just "MM/DD/YY", i.e., drop the
hours, mins and secs? Please note that in my table, months and days
under 10 do not have a leading zero. E.g., I have "6/5/04" instead
of "06/05/04". So I can't just truncate after eight characters. I'm
hoping I can specify so formula or function in the Criteria field for
this value in the query. Thanks in advance.

DateTimes are always stored exactly the same way. Any formatting you see is
strictly a display attribute.

If you want to extract the date portion without the time use DateValue()
function.

WHERE DateValue(YourDateField) = ...

It would actually be more efficient to use a criteria range so that the time
value doesn't matter, but for smaller tables the DateValue() function will do
the job.

 

[Douglas J. Steele]

Unfortunately, the Format function converts the date to a string, so you'll
no longer be able to sort on it.

As well, there's no reason to include the DateValue function if you're
wrapping the Format function call around it.