2

I'm working on a submission form for events that once completed, goes to a processing page and updates a database.

I have fourteen fields, several are required one of which is the date and time of the event.

During the processing of the form I join the two form entries, date and time before attempting the insert into the database but an error occurs.

Data truncation: Incorrect datetime value

Here is the code elements that are failing:

<cfset insdate = form["date"] & form["time"]>
<cfset new_date = #CREATEODBCDATETIME(insdate)#>

<cfif len(trim("#institle#"))>
        <cfquery name="modify">
            INSERT INTO table
            SET
            title = <cfqueryparam                      
                     cfsqltype="CF_SQL_LONGVARCHAR" 
                     value="#institle#">,
            dateTime = <cfqueryparam 
                     cfsqltype="CF_SQL_LONGVARCHAR" 
                     value="#new_date#">,
            location = <cfqueryparam 
                     cfsqltype="CF_SQL_LONGVARCHAR" 
                     value="#inslocation#">,
            category = <cfqueryparam 
                     cfsqltype="CF_SQL_SMALLINT" 
                     value="#inscategory#">,
            type = <cfqueryparam 
                     cfsqltype="CF_SQL_TINYINT" 
                     value="#instype#">                                   
         </cfquery>
</cfif>

I've trimmed the code above to make it shorter and easier to read. If anyone has any ideas what I'm doing wrong, that would be great.

I'm running Coldfusion 8, a mySQL database and the database accepts datetime on the field in question, in a yyyy-mm-dd hh:mm:ss format.

Cheers.

BoBFiSh
  • 102
  • 9
  • 1
    For your dateTime value you have specified `cfsqltype="CF_SQL_LONGVARCHAR"`. Try `cfsqltype="CF_SQL_TIMESTAMP"` instead. – Miguel-F Sep 23 '15 at 15:01
  • 1
    *the database accepts datetime on the field in question, in a yyyy-mm-dd hh:mm:ss format* While technically it may accept strings, date/time values are almost always stored as numbers internally. For consistent results, you should use `cf_sql_timestamp` (date and time) - as Miguel suggested OR `cf_sql_date` (for date only). Also, ignoring validation for a moment - did you verify `new_date` actually contains the expected date object? I noticed there is no space in between form.date and form.time. – Leigh Sep 23 '15 at 15:11
  • Thank you both. I had got fixated on the issue of combining the two form elements together that this totally by-passed me completely. – BoBFiSh Sep 23 '15 at 15:20
  • Actually, you do combine the two form fields together, inside the parseDateTime function. – Dan Bracuk Sep 23 '15 at 16:05
  • Sounds like he was saying he forgot about the correct cfsqltype for date/time values. – Leigh Sep 23 '15 at 16:17
  • Hi Leigh, that's right. Just going to read through the other replies on here now as I've been away for several days. – BoBFiSh Sep 28 '15 at 07:04

2 Answers2

1

ColdFusion can handle string representations of several datetime formats using cfSqlType="CF_SQL_TIMESTAMP", as already suggested. There is no need to create a date(time) object for the query at all. Just make sure that isDate(yourDateTimeString) returns true for the string (because that's what CF_SQL_TIMESTAMP will assume) and be aware of differences in the locale. (ddmmyyyy and mmddyyyy are two obnoxious formats that will be mixed up by ColdFusion, I guarantee it.)

Alex
  • 7,743
  • 1
  • 18
  • 38
  • Also true! you can simply check yoru form inputs to see if they are date and/or time in your validation routine. if it is you don't need the ODBC date time string. which is really for a particular kind of _driver_ and formats as { d '...'} I think... those brackets would fail in most cases anyway. – Mark A Kruger Sep 23 '15 at 18:19
  • 1
    *There is no need to create a date(time) object for the query at all* I disagree. Technically, yes - you could just pass in a *properly* formatted date string. However, given CF's notoriously "broad" interpretation of what it considers a valid date string, you are much better off doing your own validation and creating date objects IMO ;-). @BoBFiSh - As far as formats, most of the standard date functions will assume US date rules. So as Alexandar mentioned, be cognizant of the current locale and when you need to use the LS functions. – Leigh Sep 23 '15 at 18:31
  • @MarkAKruger - Yeah, but the string format does not come into play if you are using it with the proper cfsqltype. Since CreateODBCDateTime returns a date object, cfqueryparam does not need to "interpret" it (as it does with strings). CF/JDBC simply passes the java.util.Date object through to the database. Granted there are plenty of other functions that also return date objects, without the "ODBC" wrapping but in context it is not much different than other date objects (Side note, the "ODBC" is a bit of a misnomer. It works with **J**DBC too ;-) – Leigh Sep 23 '15 at 19:03
  • @Leigh Yes, if one is serious about dealing with datetime stuff, you are better off having a real date parser, not that shitty `coldfusion.runtime.OleDateTime.parseCrazyDate()`. – Alex Sep 23 '15 at 19:59
  • @AlexanderKwaschny - Heh, my affectionate nickname for it is `rollTheDiceWithDates()` ;-) It does a fair job with *US* date strings, but since most of the date functions do not support an *input* mask, it still leaves a lot up to the whims of CF. IMO, the saner course of action is to use a more robust validator/parser and create date *objects*. As long as you are passing date objects into `` the results will be consistent. No need to worry about CF, or your database, being "helpful" and misinterpreting your date strings ;-) – Leigh Sep 23 '15 at 21:07
  • 1
    @Leigh The method is actually really named `parseCrazyDate`. Adobe knew what they were doing. ;) – Alex Sep 23 '15 at 21:11
  • @AlexanderKwaschny - Oh I know... but after using it (via CF) for years one has to wonder if they were talking about the input... or the users ;-) – Leigh Sep 23 '15 at 21:30
  • I dunno Leigh - I use one or the other - string _always_ works, but it seems like I have more trial and error getting it right with the CF_SQL_TIMESTAMP. :) – Mark A Kruger Sep 24 '15 at 13:16
  • @MarkAKruger - If you have *never* had problems relying on both CF's weird string *and* a database to properly interpret date strings, well .. all I can say is you should buy a lottery ticket ;-) – Leigh Sep 26 '15 at 00:58
  • @AlexanderKwaschny I've used your method here and it has worked perfectly. Thank you for this and everyone's responses. Been really useful. – BoBFiSh Sep 28 '15 at 08:03
1

Just cut to the chase here. Alter your code to:

<!--- create a date object --->
<cfset new_date = CREATEODBCDATETIME(insdate)>
<!--- format for the DB --->
<cfset new_date = dateformat (new_date, 'yyyy-mm-dd') & ' ' & timeformat(new_date,'HH:mm:ss')>

See if that inserts for you. most DBs take a string and implicitly convert to dattime internally.

Mark A Kruger
  • 7,183
  • 20
  • 21
  • See my comment below. alex is right that you don't really _need_ the createodbcdatetime() function... you also don't need the pound signs in your cfset statement. – Mark A Kruger Sep 23 '15 at 18:21
  • Assuming they are using cfsqltype="cf_sql_timestamp", that will force a lot of unnecessary (implicit) conversions: 1. string=>date 2. date=>string 3. string=>date. (Not to mention, places where the conversions can go awry ;-) Ignoring validation for the moment, just pass the date object returned by CreateODBCDateTime into cfqueryparam and that is it. – Leigh Sep 23 '15 at 19:31
  • *most DBs take a string and implicitly convert to dattime internally.* True, but in general [relying on implicit conversion is not a good idea](http://stackoverflow.com/questions/27049918/coldfusion-parameterizing-a-querie/27066113) IMO. Always validate and use date objects, so you know exactly what the results will be. (Sorry, too tired to write up a "formal" answer, but lots of posts in my profile about dates :) – Leigh Sep 23 '15 at 19:32
  • The advantage is (if there _is_ an advantage) that you can always be absolutely sure of the representation passed to the DB. The date object parsing in CF is wierd at times - with what it thinks is/is not a date and how it presents it. At least in my experience. – Mark A Kruger Sep 24 '15 at 13:18
  • That still does not avoid the issue of CF's crazy date string parsing. Sure, `DateFormat` may accept and return a string, but CF is still applying its weird parsing logic in the background. Before `DateFormat` can apply the mask, CF must first interpret the date string and convert it into a date *object*, and it gives you no control over how it does that. So although `DateFormat` may return "some" result, you could still end up sending the wrong value on to the db. (cont'd) – Leigh Sep 25 '15 at 00:37
  • For example, `dateFormat("0/1", "yyyy-mm-dd")` produces a perfectly valid date string, but not one you would expect. Granted the same applies to most, if not all, of the standard date functions. However, given CF's propensity for creative interpretation, you are better off using a more robust library/method for parsing date strings IMO. That said, the only way to be absolutely certain the correct date is submitted to the database is to use date objects. Unlike strings, date objects are submitted as non-ambiguous numbers like ie 1442173906219, so the results are always consistent. – Leigh Sep 25 '15 at 00:39
  • Ah... gotcha... typically any form passes through a validation routine - including one that checks for date types - usually a regex utility function. – Mark A Kruger Sep 28 '15 at 18:53