Format Date in SQL Query

[jhrBanker]

How can I format a date field in a SQL query.
Current code is:

SELECT Co, Cntr, Filedate
FROM CD

UNION SELECT Co, Cntr, Filedate
FROM CDPS

ORDER BY Co, Cntr;

 

[Allen Browne]

Format the date for what purpose?

If it's for export to a text file, use the Format() function around the
date/time field, e.g.:
SELECT Co, Cntr, Format(FileDate, "Long Date") ...

However, if it's for use within Access, this is not a good idea, as the
results will sort like text and cannot be used again as a date without
Access having to convert them back into its own date/time format. In this
case, you would be far better to set the Format property of the text box on
the form or report.

 

[Michel Walsh]

Hi,

You do not have to format a date FIELD (unless you want so, for presentation, but then, it may be
preferable to leave the FORM doing it, not the query). For a date CONSTANT, in Jet, use the #
delimiter, and a US format (mm-dd-yyyy hh:nn:ss):


SELECT Now( ), MyDateTimeField, #12-31-1999 18:06:11# As SomeConstant FROM ...


Hoping it may help,
Vanderghast, Access MVP