> Some of you may have encountered the following error with date/time fields in > MySQL: > Error Executing Database Query. > > Cannot convert value '0000-00-00 00:00:00' from column 7 to TIMESTAMP. > > > This is usually resolved by adding the following to the advanced Datasource > settings > under 'Connection String' in CF Admin: > noDatetimeStringSync=true&zeroDateTimeBehavior=convertToNull > > > My questions is: does anyone have a more elegant solution to this? Is there a > way to > avoid this error without having to remember to edit new datasources first? > And should > this error even be thrown - after all the value is not an invalid date/time > value so why > does CF have issues with it?
First, that is in fact an invalid date/time value. Valid date/time values resolve to an actual date/time. Second, the general approach to handling this sort of problem is to explicitly pass NULLs to the database if you don't have a corresponding actual value. So, if someone leaves a field blank, you send NULL instead of an empty string, etc. If you're using CFQUERYPARAM (as you certainly should be unless you're using stored procedures) you can use the NULL attribute with a Boolean expression that resolves to true if the field is empty: <cfqueryparam ... null="yesNoFormat(not len(trim(form.datefield)))"> Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:329289 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
