<cfqueryparam> and datetime columns

[Ronan Lucio]

Hi,

I don't know if it's an expected behavior:

If I have a variable formated as "yyyy-mm-dd HH:mm:ss", and INSERT into db as cf_sql_date, it stores "yyyy-mm-ss 00:00:00"

I know date is supposed to be date, but we have to note CFML doesn't have a cf_sql_datetime.
So how to deal with datetime columns?

---------------------------
<cfset variables.dateTime = LSDateFormat(Now(), "yyyy-mm-dd") & " " & LSTimeFormat(Now(), "HH:mm:ss") >

<cfquery datasource="#config.getDSN()#">
    insert
    into test (date1, date2)
    values (<cfqueryparam value="#variables.dateTime#" cfsqltype="cf_sql_date">,
            <cfqueryparam value="#variables.dateTime#" cfsqltype="cf_sql_timestamp">)

</cfquery>
---------------------------

date1 stores as "yyyy-mm-dd 00:00:00"
date2 stores as "yyyy-mm-dd HH:mm:ss"

Could anyone test it into ACF?

Thank you,
Ronan

[Todd Rafferty]

I've always just used cf_sql_timestamp.

--
~Todd Rafferty ** Volunteer Railo Open Source Community Manager ** http://getrailo.org/

[Ronan Lucio]

Hi Todd,

No problem using timestamp.
I just worry about compatibility issues, supposing the actual code were storing date and time properly. But I'm not sure about that.

Thank you,
Ronan

2010/10/14 Todd Rafferty <to...@getrailo.org>

[Leigh]

>> I know date is supposed to be date, but we have to note CFML doesn't
>> have a cf_sql_datetime.

The equivalent is cf_sql_timestamp. The cfsqltypes are just a wrapper around the types used by jdbc ie java.sql.Types. (So you can probably blame java for the inconsistent naming convention.)

Types:
     cf_sql_date => java.sql.Types.DATE => java.sql.Date
     cf_sql_timestamp => java.sql.Types.TIMESTAMP => java.sql.TimeStamp

Description:
   - DATE consists of day, month, and year.
   - TIMESTAMP combines DATE and TIME and also adds in a nanosecond field.

http://download.oracle.com/javase/1.3/docs/guide/jdbc/spec/jdbc-spec.frame8.html

So the behavior you are seeing is expected. 

>> So how to deal with datetime columns?

It depends on your database and what you are storing: date or date and time. Since I use MS SQL 2005, I prefer to cf_sql_timestamp all the time, and truncate the time with CF functions when needed.

-Leigh

[Ronan Lucio]

Hi Leigh,

Thank you very much for your answer.
It's supposed to answer the question.

Thanks,
Ronan

2010/10/14 Leigh <cfsea...@yahoo.com>

[Leigh]

>> It's supposed to answer the question.

Well, I do not know if it _did_ or not .. ;-) But hopefully it conveyed: a) that is the correct behavior and b) the same behavior exists on ACF -Leigh