Change date format in query

[mtdew]

Is it possible to change a date format from:

yyyy-mm-dd 00:00:00 to mm/dd/yy

in a query? And also to have the today's date in the field if the field is empty?

The data is linked to an Excel file so I'm not able to make changes from within Access. So would that prevent the query from changing the format or data in the field prior to placing it in a new table?

 

[SkipVought]

hi,

Dates are PURE NUMBERS.

What in your query, do you need to change the format?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mtdew]

The field is in the format yyyy-mm-dd 00:00:00 but when I run the query and append the records to another database the format needs to be mm/dd/yy.

 

[SkipVought]

Well, if it is really a date, then the format is simply a display issue, related to the FIELD that the appended data is in.

Check the Data Type of that field.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[FledglingAnalyst]

Have you tried using Format? This will change the output on the query.

Select your_table.your_date
From your_table
Format([your_date],"MM/DD/YY")

 

[dhookom]

FledglingAnalyst,
You should try your suggestions prior to posting. Format() is a good option but not following the "FROM" clause.

Duane
Hook'D on Access
MS Access MVP

 

[FledglingAnalyst]

Dhookom,

You are absolutely correct. My apologies. I should of taken the time to make sure that it would work in a query.

This works:

SELECT Format([your_date],"MM/DD/YY") AS [TheDate]
FROM your_table;