timestamp in cfqueryparam

[kt03]

Hi, i have the filed with smalldatime format. from my query, i have: between '#TimeFormat(form.d__stage_time_startTime, "HH:mm")#'  and '#TimeFormat(form.d__stage_time_endTime, "HH:mm")#' resutls A:  Correct between '10:00'  and '12:00' But when i added cfqueryparam   and  I didn't get the same results and it passing something below which was not Correctted. i've try cf_sql_time or cf_sql_timestamp but none of them work just like the one i have from results A. resutls B: Parameter #1(cf_sql_time) = {ts '1899-12-30 10:00:00'} Parameter #2(cf_sql_time) = {ts '1899-12-30 12:00:00'} what can i do in cfqueryparam to get the same results as passing like from results A? thanks

[Dan_Bracuk]

I assume that in your query, you are using a database function of some sort to extract the time portion of the datetime field. 

Timeformat returns a string, not a time object.  The first thing I would do is to use CreateTime instead.  I would also use cfsqltype="cf_sql_time".  Then it should work properly.

[kt03]

create time?, it takes 3 parameters, what can i do here?

thanks

[BKBK]

If you strictly want time values, then you shouldn't be mixing times with datetimes. Why not use CHAR as the datatype? Here is an example:

<cfset t = "12:34">

<cfquery name="q" datasource="myDSN">

insert into myTBL(timeCol) values(<cfqueryparam cfsqltype="cf_sql_time" value="#t#">)

</cfquery>

This would work when the field timeCol has datatype CHAR(8) or TIME (I am on MySQL). The time would then be stored as 12:34:00.