Format your datetime values directly in your SQL query.
Why?
Formatting datetimes directly in your query is significantly faster than using dateFormat() and timeFormat() in ColdFusion (and most other languages) and simplifies your query output by having formatting already completed.
How?
Formatting datetimes is incredibly simple. All you need do is call Convert() and specify the format parameter you wish to use.
SELECT top 1 convert(varchar(10), birthdate, 101) AS birthdate FROM employees
Notice the style parameter of 101. This returns the date in US Standard format mm/dd/yyyy. A plethora of style parameters are available including options for times and standard formats of countries outside the US. Check MSDN for a complete listing and more information on the Convert method.
SQL Function, MSDN Ref.
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Related posts:
- Convert datetimes to integers when doing date-only range searches in SQL. From comment: 12/22/09 I discovered later on, this is actually...
- Use cftimer to help optimize specific portions of code. Why? cftimer provides a simple and easy way to determine...
Some useful conversions if you want times only.
Military (24-hour) format:
CONVERT(char(12), the_date, 14) hh:mi:ss:mmm
CONVERT(char(8), the_date, 14) hh:mi:ss
CONVERT(char(5), the_date ,14) hh:mi
AM/PM:
LTRIM(RIGHT(CONVERT(char(26), the_date, 9), 14)) h:mi:ss:mmmTT
REPLACE(RIGHT(CONVERT(char(26), the_date, 9), 14), ‘ ‘, ‘0′) hh:mi:ss:mmmTT (leading zero on hour)
Unfortunately, getting hh:mm:ssTT involves alot of string operations - probably more than is worth doing to be honest…
LTRIM(RIGHT(CONVERT(char(19), the_date, 0), 7)) h:miTT
REPLACE(RIGHT(CONVERT(char(19), the_date, 0), 7), ‘ ‘, ‘0′) hh:miTT (leading zero)