Skip to main content
_
_G_1
Inspiring
January 25, 2012
Answered

Inserting Date and Time Issues

  • January 25, 2012
  • 3 replies
  • 1573 views

Hi All,

I have a very simple select query as follows... UPLOAD_DATE is held in table1 in the following format:     23/01/2012 13:04:36

<cfquery name="test" datasource="test">

SELECT  UPLOAD_DATE

FROM     TABLE1

</cfquery>

and I loop around the above query to insert into another table...

<cfloop query="test">

     <cfquery name="make_tfer2" datasource="test">

          INSERT INTO TABLE2

               (UPLOAD_DATE)

          VALUES

               (#test.UPLOAD_DATE#)

     </cfloop>

</cfloop>

But, when I insert into table2 it inserts the date as:  23/01/2012 .... How do I correct this code so I can insert the date as:   23/01/2012 13:04:36 ????

    This topic has been closed for replies.
    Correct answer Dan_Bracuk

    Your approach is inefficient.  Instead of looping through query results and doing individual inserts, do a single sql insert.

    insert into table2

    select whatever

    from table1

    For your post at 04:48, of course there is no time component.  The example you showed has 00:00 as the time.

    3 replies

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    January 25, 2012

    \'G\' wrote:

    Hi All,

    I have a very simple select query as follows... UPLOAD_DATE is held in table1 in the following format:     23/01/2012 13:04:36

    <cfquery name="test" datasource="test">

    SELECT  UPLOAD_DATE

    FROM     TABLE1

    </cfquery>

    and I loop around the above query to insert into another table...

    <cfloop query="test">

         <cfquery name="make_tfer2" datasource="test">

              INSERT INTO TABLE2

                   (UPLOAD_DATE)

              VALUES

                   (#test.UPLOAD_DATE#)

         </cfloop>

    </cfloop>

    But, when I insert into table2 it inserts the date as:  23/01/2012 .... How do I correct this code so I can insert the date as:   23/01/2012 13:04:36 ????

    Two suggestions (just to answer the immediate question):

    1) Make sure the datatype of the column Upload_date in Table2 is datetime (not just date).

    2) Try something like

    <cfoutput query="test">

        <cfset date=dateFormat(parseDatetime(upload_date),"dd/mm/yyyy hh:mm:ss")>

        <cfquery name="make_tfer2" datasource="test">

            INSERT INTO TABLE2 (UPLOAD_DATE)

            VALUES #date#

        </cfquery>

    </cfoutput>

    D
    Dan_BracukCorrect answer
    Inspiring
    January 25, 2012

    Your approach is inefficient.  Instead of looping through query results and doing individual inserts, do a single sql insert.

    insert into table2

    select whatever

    from table1

    For your post at 04:48, of course there is no time component.  The example you showed has 00:00 as the time.

      Owainnorth
      Owainnorth
      Inspiring
      January 25, 2012

      Use CFQUERYPARAMs, they will solve all your worldly problems.

      _
      _G_1Author
      Inspiring
      January 25, 2012

      Thank you for your reply Owain, how do you mean tho do you have an example of how I could use cfqueryparam in this instance ?

      I have tried adding   <cfqueryparam value=#test.UPLOAD_DATE# cfsqltype="CF_SQL_DATE">  to the second query but it stills inserts as dd/mm/yyyy

      Owainnorth
      Owainnorth
      Inspiring
      January 25, 2012

      What's the datatype of the column [upload_date] in the [table1] table? Hopefully it's date/datetime/timestamp. If so, do a cfdump of your first query:

      <cfdump var="#test#" />

      You should see the values shown as timestamps, which CF shows in braces. Something like {ts:1900-01-01 23:00:00.000}

      Assuming you see that, you do indeed have a date/time object. To do the insert, do this:

      <cfquery datasource="test">

        INSERT INTO table2 ( upload_date )

        VALUES ( <cfqueryparam cfsqltype="cf_sql_timestamp" value="#UPLOAD_DATE#" /> )

      </cfquery>

      That should sort your issues, assuming the datatype of the target table is also date/datetime/timestamp.

      HTH.

      Terms & ConditionsAccessibility statement
      Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices