9 Replies Latest reply on Jan 29, 2016 4:56 PM by ConnecticutRick

    invalid date or time string

    cfsetNewbie Level 1

      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

        • 1. Re: invalid date or time string
          Reed Powell Level 3

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

          -reed

          • 2. Re: invalid date or time string
            cfsetNewbie Level 1

            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.

            • 3. Re: invalid date or time string
              -==cfSearching==- Level 4

              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?

              • 4. Re: invalid date or time string
                JR "Bob" Dobbs Level 4

                If you run into an invalid date value in your CSV do you want to substitute a NULL value?  You could do something like:

                 


                <cfqueryparam cfsqltype="cf_sql_date" value="#listgetAt(i,9)#" null="#YesNoFormat( IsDate(listgetAt(i,9)) neq true  )#">

                • 5. Re: invalid date or time string
                  JR "Bob" Dobbs Level 4

                  You might also try using cfsqltype="cf_sql_timestamp".  That is the cfsqltype that works for MS SQL DateTime fields, it may also be appropriate to use in MS Access.

                  • 6. Re: invalid date or time string
                    Irish-Phoenix Level 1

                    I fixed the date time problem, but now this code is giving me a data mismatch..


                    It's all correct from what I see.. it says the problem is in line 60, that's the last cfqueryparam in the input. It is marked as a text field

                    with the number 10 in it and the cfqueryparam is set as variablechar.

                     

                    Here is my error:


                    Error Executing Database Query.

                     

                    [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

                    The error occurred in C:\websites\211562fe3\partners\cfm\csvUploadAction.cfm: line 60

                    58 :                    '#listgetAt('#index#',9, ',')#',
                    59 :                    '#listgetAt('#index#',10, ',')#',
                    60 :                    '#listgetAt('#index#',11, ',')#') 
                    61 :    </cfquery> 
                    62 : </cfloop> 
                    

                     

                    This is my query now:
                    <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="#listgetAt(i,9)#" null="#YesNoFormat( IsDate(listgetAt(i,9)) neq true  )#">,
                                    <cfqueryparam cfsqltype="cf_sql_date" value="#listgetAt(i,10)#" null="#YesNoFormat( IsDate(listgetAt(i,10)) neq true  )#">,
                                     <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,11)#">
                                      )
                       </cfquery>

                     

                    and this is the line from the .csv file I'm trying to upload into the database:
                    12,active,Douglas,L,Berg,none,Marshall,MN,Male,3/3/1952,3/3/1952,none

                    It should all go in with no problem, what am I missing here?
                    Thank you for all the help so far. It seems to have satisfied the date problem.

                    • 7. Re: invalid date or time string
                      JR "Bob" Dobbs Level 4

                      Please post more of your code:

                       

                      1. More of your code.  It would be useful to see how your loop is executing.

                       

                      2. The definition of your table structure.

                      • 8. Re: invalid date or time string
                        Dan Bracuk Level 5

                        Regarding:  The definition of your table structure.

                         

                        Especially the siteid field.

                        • 9. Re: invalid date or time string
                          ConnecticutRick

                          Thanks for this. I forgot I had a header row in the spreadsheet.