Mysql date and time fields with Coldfusion

Posts: 21 · Posted: Tue Feb 19, 2008 10:08 pm

I spent a lot of time troubleshooting the output of a cfquery from a mysql 5 date type and a time type field. I searched high and low to find an answer but found very little on the topic. The purpose of this post is to help anyone that might be having the same problem.

Problem:
When selecting the date or the time my output looked like this
for date

ts {2008-17-02 00:00:00}

for time

ts {000-00-00 16:47:23}

When inserting a date or time an error occurs stating the data is not the correct format.

Cause:
The problem was coldfusion doesn't automatically convert the data into date or time format. The output is the actual odbc time format as read from the Coldfusion jdbc driver.

Solution:
Use DateFormat() to output and insert the date; use TimeFormat() to output or insert the time.
to output

Code:

#DateFormat(date,"M,dd y")#

to insert

Code:

#DateFormat(date,"yyyy-mm-dd")#

to output

Code:

#TimeFormat(time,"h:mm tt")#

to insert

Code:

#TimeFormat(time,"hh:mm:ss")#

note: mysql uses the 24 hour format. If you do not use hh:mm:ss all dates will be entered as AM.

Posts: 543 · Posted: Wed Feb 20, 2008 12:32 am

When inserting/updating you can also use

Code:

CreateODBCDateTime(Now())

To assure proper date/time conversion.