Skip to main content
K
kt03
Inspiring
September 26, 2011
Question

timestamp in cfqueryparam

  • September 26, 2011
  • 2 replies
  • 1276 views

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

This topic has been closed for replies.

2 replies

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
September 27, 2011

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.

D
Dan_Bracuk
Inspiring
September 26, 2011

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.

K
kt03Author
Inspiring
September 26, 2011

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

thanks

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices