Skip to main content
Known Participant
January 5, 2011
Question

invalid date or time string

  • January 5, 2011
  • 1 reply
  • 6808 views

I am trying to put data into my db that is a date string from a .csv file, this is the format it's in from the file: 3/3/1952 (example)

this is my query:

<cfquery name="importcsv" datasource="#APPLICATION.dataSource#">
         INSERT INTO employees (siteID,empstatus,empfirstname,empmiddlename,emplastname,empnickname, emplocation,empgender,empdob,empdoh,empee)
         VALUES
                  (<CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#trim(listgetAt(i,1))#">,
                    <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,2)#">,
                    <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,3)#">,
                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#trim(listgetAt(i,4))#">,
                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,5)#">,
                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,6)#">,
                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,7)#">,
                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,8)#">,
                 <CFQUERYPARAM cfsqltype="cf_sql_date" VALUE="#DateFormat(CreateODBCDate(ParseDateTime(listgetAt(i,9))), 'MM/DD/YYYY')#">,
                 <CFQUERYPARAM cfsqltype="cf_sql_date" VALUE="#DateFormat(CreateODBCDate(ParseDateTime(listgetAt(i,10))), 'MM/DD/YYYY')#">,
                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,11)#">
                  )
   </cfquery>

This is the line giving me the problem:

<CFQUERYPARAM cfsqltype="cf_sql_date" VALUE="#DateFormat(CreateODBCDate(ParseDateTime(listgetAt(i,9))), 'MM/DD/YYYY')#">,

Can anyone help me get thi sworking properly? I've been looking at all kinds of documents and web tricks and it's not working.

Thank you

This topic has been closed for replies.

1 reply

Inspiring
January 5, 2011

Please show us a sample line of the data file, and also tell us the CF error that you are getting.

-reed

Known Participant
January 5, 2011

Sorry, this is the error:

empdob is an invalid date or time string.

The error occurred in f:\websites\211562fe3\partners\cfm\csvUploadAction.cfm: line 58

56 :                  <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,7)#">,
57 :                  <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,8)#">,
58 :                  <CFQUERYPARAM cfsqltype="cf_sql_date" VALUE="#DateFormat(CreateODBCDate(ParseDateTime(listgetAt(i,9))), 'MM/DD/YYYY')#">,
59 :                  <CFQUERYPARAM cfsqltype="cf_sql_date" VALUE="#DateFormat(CreateODBCDate(ParseDateTime(listgetAt(i,10))), 'MM/DD/YYYY')#">,
60 :                  <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,11)#">

The rest of the code is in my other post, the date appears in the .csv file like this: 00/00/0000 and it won't take it in my query, the table is also set for date and it's an access database.

Inspiring
January 5, 2011

empdob is an invalid date or time string.

The error seems pretty clear. The value "empDob" is not a date. Perhaps your file has a header row you are inadvertently trying to load?