Inserting Date and Time Issues

[_G_1]

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 ????

[Owainnorth]

Use CFQUERYPARAMs, they will solve all your worldly problems.

[_G_1]

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]

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.

[_G_1]

I am using Oracle and the datatype is infact of data type DATE... I outputted using cfdump and this is what is shown:  {ts '2012-01-25 00:00:00'}

My second query now looks like the below but it is still inserting as dd/mm/yyy with no time :S

<cfloop query="test">

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

          INSERT INTO TABLE2

               (UPLOAD_DATE)

          VALUES

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

     </cfloop>

</cfloop>

[Dan_Bracuk · Accepted solution]

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.

[BKBK]

\'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>