converting date format from dd-mm-yyyy to yyyy-mm-dd

[Paul]

How can I change the date format from dd-mm-yyyy to yyyy-
mm-dd in a query ?

Thanx

 

[Allen Browne]

Paul, there are 2 aspects to your question: what is displayed, and what is
stored.

Access stores dates as numbers, where the integer part represents the date,
and the fraction part the time of day (.5 = noon, .25 = 6am, etc.) The date
is therefore not stored in any format.

By default, Access displays the date to you in the format specfied under
Regional Settings in the Windows Control Panel. However, literal dates in a
SQL statement are stored/interpreted like American dates by default. You
cannot change this behavior, but you can specify a display format for a
table field (or a control on a form/report).

More information on avoiding the 3 cases where Access is likely to
misunderstand dates in dd-mm-yyyy format:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

 

[Paul]

Allen,

Thanx for your explanation. What happens next is when I
export my query to a XML file, it automatically adds the
time to the date in this file, even when I put my format
to short date. Do you know how to change this ?

Thanx again !

 

[Rick Brandt]

Allen,

Thanx for your explanation. What happens next is when I
export my query to a XML file, it automatically adds the
time to the date in this file, even when I put my format
to short date. Do you know how to change this ?

Use the Format() function rather than a format property. That will convert
the date to a string in the format you specify and it will export as such.

 

[Sming]

Hi Paul,

Not long ago, I was having the same problem and I posted
the question on the newsgroup bulletin. These is one of
the answers I got,

DateForExport: Format([YourDateField],"yyyymmdd")

It definitely leaves out the time.

Sming