just another ColdFusion Blog: 0000-00-00 00:00:00 to TIMESTAMP

0000-00-00 00:00:00 to TIMESTAMP

Jan 27 2010

posted by Gary Funk in ColdFusion, SQL | 4 Comments

I read ColdFusion Bloggers every day to learn what is going on in the ColdFusion community. Today I came across a blog entry that solved an old problem with MySQL and the zero timestamp.

In MySQL, if a timestamp is blank, it is set to 0000-00-00 00:00:00 and this causes a problem for ColdFusion, which will return an error of: Cannot convert value '0000-00-00 00:00:00? from column X to TIMESTAMP. Thanks to John Sieber at Thoughts from the mountains we have the simple answer. IN the ColdFusion Administrator, go to Data Sources, edit the MySQL (4/5) data source, click on Show Advanced Settings and enter

view plain print about

1noDatetimeStringSync=true&zeroDateTimeBehavior=convertToNull

in the Connection String dialog box.

This will make my job a little easier.

Thanks John.

This entry was posted on January 27, 2010 at 2:26 PM and has received 1063 views. There are currently 4 comments. Print this entry.

TweetBacks

Comments

[Add Comment] [Subscribe to Comments]

#1 by John Sieber on 1/27/10 - 3:45 PM

Glad that the post was helpful!
#2 by Ben Nadel on 1/27/10 - 5:05 PM

Sweet! I have run into this problem when copying data from one database to another; I still have no idea why it happens, but it's good to know there is connection string to help mitigate it.
#3 by Spills on 1/28/10 - 10:41 AM

Actually I think the problem is that in MYSQL 5.0x at least 0000-00-00 00:00:00 is an invalid range.
Timestamp range is '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' AND
Date range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
see: http://dev.mysql.com/doc/refman/5.0/en/datetime.ht...
#4 by Spills on 1/28/10 - 10:55 AM

I should have been clear CF should handle the special value 0000-00-00 00:00:00 that IS allowed MYSQL but it doesn't because normally it would be invalid range and your post is great information to get around the issue. Thanks

0000-00-00 00:00:00 to TIMESTAMP

TweetBacks

Comments

NAVIGATION

Subscribe