User Guide

Query of Queries user guide 511
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.
Understanding Query of Queries performance
Query of Queries performs very well on single-table query objects that were accessed directly
from a database. This is because ColdFusion MX stores meta information for a query object
accessed from a database.
When working with a query resulting in a SQL join, Query of Queries performs as follows:
• Query of Queries is very efficient for simple joins in which there is only one equality between
two column references or constants; for example:
SELECT T1.a, b, c, d FROM T1, T2 WHERE T1.a = T2.a
SUM SYSTEM
USER
TABLE TEMPORARY THEN
TIME TIMESTAMP TIMEZONE
HOUR
TIMEZONE_MINUTE TO
TRAILING TRANSACTION TRANSLATE TRANSLATION TRIM
TRUE UNION UNIQUE UNKNOWN UPDATE
UPPER USAGE USER USING VALUE
VALUES VARCHAR VARYING VIEW WHEN
WHENEVER WHERE WITH WORK WRITE
YEAR ZONE