Using Queries of Queries with dates

If you create a query object with the QueryNew function and populate a column with date constants, ColdFusion MX stores the dates as a string inside the query object until a Query of Queries is applied to the query object. When ColdFusion applies a Query of Queries to the query object, it converts the string representations into date objects.

Query of Queries supports date constants in SQL and ODBC format, as follows:

• SQL format Dates, times, or timestamps in one of the following format:
  • Date string yyyy-mm-dd, for example, 1955-06-13.
  • Time string hh:mm:ss[.[nnn]], for example, 14:34:30.75.
  • Timestamp string yyyy-mm-dd hh:mm:ss[.[nnn]], for example, 1924-01-14 12:00:00.000.
• ODBC format Dates, times, or timestamps in one of the following format:
  • Date string {d 'value'}, for example, {d '2004-07-06'}.
  • Time string {t 'value'}, for example, {t '13:45:30'}.
  • Timestamp string {ts 'value'}, for example, {ts '2004-07-06 13:45:30'}.

Tip: If you want to convert the date to its original format, use the DateFormat function and apply the "mm/dd/yy" mask.

View comments in LiveDocs