Format Date in SQL Query

  • Thread starter Thread starter jhrBanker
  • Start date Start date
J

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;
 
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.
 
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
 
Back
Top