Format Date

[Hillary]

I want to change the format of a birthdate in a query. The date comes over as m/d/yyyy. I would like it to be in mm/dd/yyyy format.

For example, I would like 1/6/2005 to read 01/06/2005.

Thanks,

Hillary

 

[PHV]

SELECT Format([dob field], 'mm/dd/yyyy') AS BirthDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cheerio]

In query design view right click on the relevant date column and select Properties. There is a format field into which you can type a format string or select from a drop down list. For example, you could choose the windows long date format to make the query portable across different countries.

PHV's approach will work but I have come to grief because I forget it delivers text. This means you cannot sort the results by that date reliably. For example I get a text sort of
31/01/2004
31/03/2001

because 01 comes before 03 but an ascending date sort should put the year 2001 first.

 

[PHV]

cheerio:
SELECT Format(DateField, 'dd/mm/yyyy')
FROM yourTable
ORDER BY DateField

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cheerio]

PHV

Thanks for the comment.

In practice I get into trouble with embedded queries in Excel workbooks using Microsoft query - my example was a simplification. Apart from sorting which applies equally in Excel there are issues such as date arithmetic failing such as turning a birthdate into an age.

In a pure Access context my technique controls the formatting without changing the datatype. It is also more robust in an international group.

Finally the use of a calculated field means that the query is not updateable on that column although that may sometimes be an advantage.

Both techniques have their advantages.