Coldfusion date format question

[Guest]

Hello All,

I have an issue inserting date and time together into a database table from coldfusion.

Right now, I am using <cfquery> to get records from a particular table, in which one of the field is date, and its value in the database is something like
"2/14/2007 9:10:12 AM". I am performing insert operation into another table which has to insert this date field into the column of new table. I am doing currently as follow:

<cfquery name="getDate" datasource="" maxrows="1">
   select date_field from table
</cfquery>

<cfif getDate.recordcount is not 0>
  <cfquery name="insertDate" datasource="">
    insert into table2 (date_field1) values (<cfqueryparam cfsqltype="cf_sql_date" value=#getDate.date_field#>)
  </cfquery>
</cfif>

The problem with this is only the date is getting inserted but not the time. How to format this.

It would be great if somebody can reply, I am not sure if my messages are getting posted, as I posted couple of issues I had some time back, but never got any reply back.

Thanks.

[ilssac · Accepted solution]

cf_sql_date data type is only the date portion of a date-time value.

cf_sql_time data type is only the time portion of a date-time value.

cf_sql_timestamp data type is the full date-time value.

[-__cfSearching__-]

    insert into table2 (date_field1) values (<cfqueryparam cfsqltype="cf_sql_date" value=#getDate.date_field#>)

Cf_sql_date only passes the date portion of a date/time value.  Use a type that includes the date and time, like cf_sql_timestamp.  Check the documentation for your database type.

funandlearning333 wrote:

I am performing insert operation into another table which has to insert this date field into the column of new table.

The better way is to insert into the second table directly, using a single query.You can limit the number of results inserted by adding a where clause, or using the limit/top operators. The operator varies by database type.

INSERT INTO Table2 (ColumnName)

SELECT  ... ColumnName

FROM    Table1