Skip navigation
jhutchdublin
jhutchdublin
Currently Being Moderated

Getting SQL error

May 30, 2007 4:49 AM

I'm getting the following error about a date and time

Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][SQL Native Client][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

The error occurred in C:\Inetpub\wwwroot\rayanne\customerinsertsql.cfm: line 5

3 :
4 : INSERT INTO booking (book_made, book_checkin_date, book_checkout_date, book_adults, book_children)
5 : VALUES('#FORM.book_made#','#FORM.book_checkin_date#','#FORM.book_chec kout_date#','#FORM.book_adults#','#FORM.book_children#')
6 :
7 : SELECT SCOPE_IDENTITY() AS theNewId;


Any ideas of how to correct this
206 Views   5 Replies   Latest reply: JR \"Bob\" Dobbs-qSBHQ2, Jun 4, 2007 11:57 AM
 
Replies
  • john85
    Currently Being Moderated
    1. john85,
    May 30, 2007 5:35 AM   in reply to jhutchdublin
    Report
    You need to convert the FORM.book_checkin_date and other date to DATE format. Currently, it is define as CHAR not DATE. In Oracle, to_date function format string as date, to_char format date as string. I am not sure how to do it SQL Server but google should be your answer.
     
    |
    Mark as:
  • Dan Bracuk
    Currently Being Moderated
    2. Dan Bracuk,
    May 30, 2007 5:50 AM   in reply to jhutchdublin
    Report
    To follow up on john85's answer, cold fusion has a number of functions that will help you. They have names like createdate, createodbcdate, etc. Since the value is coming from a form, it's a good idea to verify that the value actually represents a valid date.
     
    |
    Mark as:
  • philh
    Currently Being Moderated
    3. philh,
    Jun 3, 2007 11:18 AM   in reply to jhutchdublin
    Report
    SQL can read and convert character-formatted dates. I suspect you have a problem with the order of date elements. What collation is SQL Server using? Do you submit dates in MM/DD/YYYY format, or DD/MM/YYYY format?

    The safe approach is to use the ODBCDate functions.
     
    |
    Mark as:
  • Dina Hess
    Currently Being Moderated
    4. Dina Hess,
    Jun 3, 2007 12:34 PM   in reply to philh
    Report
    use <cfqueryparam cfsqltype="cf_sql_date" value="#form.book_checkout_date#">; in fact, it's best practice to use cfqueryparam for all of your query parameters; also, if you're date is actually a timestamp data type in the database, cfsqltype="cf_sql_timestamp" should be used instead.
     
    |
    Mark as:
  • JR \
    Currently Being Moderated
    5. JR \"Bob\" Dobbs-qSBHQ2,
    Jun 4, 2007 11:57 AM   in reply to Dina Hess
    Report
    I agree with Hess that you should use cfqueryparam. The cfsqltype attribute should be "cf_sql_timestamp" for DATETIME fields in Microsoft SQL Server.

    SQL will implicitly convert strings that contain dates to datetime values. I'd verify that your form variables contain valid dates. What data type are your date fields and what values do your form variables contain?
     
    |
    Mark as:
Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)