If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date Format Conversion
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. |
#2
|
|||
|
|||
Date Format Conversion
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())),"m m/dd/yy") Tman "Paputxi" wrote: 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. |
#3
|
|||
|
|||
Date Format Conversion
Paputxi wrote:
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. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#4
|
|||
|
|||
Date Format Conversion
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. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "TomTait747" wrote in message ... 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())),"m m/dd/yy") Tman "Paputxi" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
convert date format | noel | General Discussion | 1 | March 26th, 2006 11:57 PM |
Date Format Issue | FA | Using Forms | 1 | January 28th, 2006 09:58 PM |
MS Query Date Format | scos00 | General Discussion | 0 | October 21st, 2005 04:58 PM |
Wrong mail merge date format | sam | Mailmerge | 1 | June 8th, 2005 01:23 PM |
Changing the Date Format | xtian_princess | Worksheet Functions | 1 | September 8th, 2004 02:54 PM |