Oracle date handling and ColdFusion on INSERT TO

To issue a “INSERT TO” with a “SELECT” statement is a common practice to copy records from one table to another. A statement like this might look like;

[code]

<cfquery datasource="mydb">

INSERT INTO mytable

(id, somefield)

SELECT '1', thefield

FROM demo

WHERE id = 1

</cfquery>

[/code]

No big deal. Al right, now many times one also adds the current date to the table. Now as ColdFusion developers you might me inclined to write this code;

[code]

<cfquery datasource="mydb">

INSERT INTO mytable

(id, somefield, thedate)

SELECT '1', thefield, '#now()#'

FROM demo

WHERE id = 1

</cfquery>

[/code]

But what a surprise the code above will not work since Oracle is not able to interpret the “#now()#’ field (yes, we are on a CFM page, but it still does not work).Simple solution is to use the Oracle timestamp with;

[code]

<cfquery datasource="mydb">

INSERT INTO mytable

(id, somefield, thedate)

SELECT '1', thefield, systimestamp

FROM demo

WHERE id = 1

</cfquery>

[/code]

The “systimestamp” will insert the current date and time for you.