3 Replies Latest reply on Sep 27, 2011 7:05 AM by BKBK

    timestamp in cfqueryparam

    kt03 Level 1

      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

        • 1. Re: timestamp in cfqueryparam
          Dan Bracuk Level 5

          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.

          • 2. Re: timestamp in cfqueryparam
            kt03 Level 1

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

             

            thanks

            • 3. Re: timestamp in cfqueryparam
              BKBK Adobe Community Professional & MVP

              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.