Skip to main content
Rodrigo Alarcon
Rodrigo Alarcon
Known Participant
December 9, 2009
Question

Make sure the date is in the correct format

  • December 9, 2009
  • 1 reply
  • 1940 views

Hey ppl, loooong time since my last post here, and this time i'm needing some help with this.

So far i had created a form that will let you filter some info based on starting date to ending date, for the dates i'm using jquery calendars and they work fantastic, the thing is that when i'm using the debugging tool i can see for example:

IT IS:

SELECT *

FROM myTable

WHERE ini_date >= '2009/03/12 00:00:00'

AND end_date <= '2009/04/12 23:59:59'

SHOULD BE:

SELECT *

FROM myTable

WHERE ini_date >= '2009/12/03 00:00:00'

AND end_date <= '2009/12/04 23:59:59'

The query works as intented, but the issue is the date it self... for this example i intented to filter using as initial date 12/03/2009 and as end Date 12/04/2009, i don't understand why, coldfusion does not let me specify the way it should be.

BTW... here we use dd/mm/yyyy format for dates and it's showind on the datefields as wanted.

Just to make things a little more understandable i'm leaving my source code here.

My Source Code
<cfparam name="form.txt_fechaDesde" default="#dateformat(DateAdd('d',-1,now()),'mm/dd/yyyy')#">
<cfparam name="form.txt_fechaHasta" default="#dateformat(now(),'mm/dd/yyyy')#">
<cfparam name="form.sel_vehiculo" default="">

<script type="text/javascript" src="includes/jquery-1.3.2.min.js"></script>
<script type="text/javascript" src="includes/date.js"></script>
<script type="text/javascript" src="includes/jquery.datePicker.js"></script>

<link href="includes/datePicker.css" rel="stylesheet" type="text/css"/>
<link href="includes/demo.css" rel="stylesheet" type="text/css"/>

<script type="text/javascript">
String.prototype.trim = function(){ return this.replace(/^\s+|\s+$/g,'') }

var currentTime = new Date();
var month = currentTime.getMonth() + 1;
var day = currentTime.getDate();
var year = currentTime.getFullYear();
var TodayDate = day + "/" + month + "/" + year
$(function(){
    $('.date-pick').datePicker({clickInput:true,createButton:true,startDate:'01/09/2009'});
});
</script>

<cfset dateFrom = CreateODBCDateTime(form.txt_fechaDesde & " 00:00:00")>
<cfset dateTo = CreateODBCDateTime(form.txt_fechaHasta & " 23:59:58")>

<cfif form.sel_vehiculo NEQ "">
    <cfquery name="qry_speed" datasource="#application.datasource#">   
        SELECT a.td_fechaEventoCL,
            td_speedKph,
            b.veh_id
        FROM tbl_tramaData a
        INNER JOIN tbl_asignaciones b on b.eq_imei = a.eq_imei
        WHERE a.td_fechaEventoCL >= #dateFrom#
        AND a.td_fechaEventoCL <= #dateTo#
        AND b.veh_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.sel_vehiculo#">
    </cfquery>
</cfif>

<cfquery name="qry_vehiculos" datasource="#application.datasource#">
    SELECT a.veh_id,
        a.veh_pat + ' - ' + a.veh_desc as veh_info
    FROM tbl_vehiculos a
    INNER join tbl_clientes b on b.cli_id = a.cli_id
    INNER join tbl_asignaciones c on c.veh_id = a.veh_id
    WHERE a.cli_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#session.cli_id#">
    ORDER BY veh_pat ASC
</cfquery>

<br />
<cfform name="frm_FilterChart" method="post" action="#cgi.SCRIPT_NAME#?#cgi.QUERY_STRING#" format="html" preservedata="yes">
    <table align="center" width="700" border="0" cellpadding="0" cellspacing="0">
        <tr>
            <td style="font-weight:bold">
                Veh&iacute;culo:
            </td>
            <td>
                <cfselect name="sel_vehiculo"
                    query="qry_vehiculos"
                    display="veh_info"
                    value="veh_id"
                    queryPosition="below">
                    <option value="">Seleccione un Veh&iacute;culo</option>
                </cfselect>
            </td>
            <td style="font-weight:bold">
                Desde:
            </td>
            <td>
                <cfinput type="text"
                    name="txt_fechaDesde"
                    id="txt_fechaDesde"
                    class="date-pick"
                    readonly="true"
                    value="#form.txt_fechaDesde#"
                    style="width:80px; font-weight:bold; background-color:##F3F3F3">
            </td>
            <td style="font-weight:bold">
                Hasta:
            </td>
            <td>
                <cfinput type="text"
                    name="txt_fechaHasta"
                    id="txt_fechaHasta"
                    class="date-pick"
                    readonly="true"
                    value="#form.txt_fechaHasta#"
                    style="width:80px; font-weight:bold; background-color:##F3F3F3">
            </td>
            <td>
                <cfinput type="submit" name="btn_filtrar" value="Aplicar Filtro">
            </td>
        </tr>
    </table>
</cfform>

<cfif form.sel_vehiculo NEQ "">
    <table align="center" width="1000" border="0" cellpadding="0" cellspacing="0">
        <tr>
            <td>
                <cfchart chartwidth="1000"
                    chartheight="600"
                    scalefrom="0"
                    scaleto="140"
                    format="png"
                    backgroundcolor="##b4b4b4"
                    databackgroundcolor="##b4b4b4"
                    foregroundcolor="##FFFFFF"
                    font="Arial"
                    showborder="no"
                    showlegend="no"
                    tipstyle="mouseOver"
                    show3d="no"
                    labelformat="number">
                    <cfchartseries paintstyle="raise"
                        datalabelstyle="none"
                        seriescolor="##000000"
                        type="line"
                        query="qry_speed"
                        itemcolumn="td_fechaEventoCL"
                        valuecolumn="td_speedKph"
                        serieslabel="Valor"/>
                </cfchart>
            </td>
        </tr>
    </table>
</cfif>
This topic has been closed for replies.

1 reply

ilssac
ilssac
Inspiring
December 9, 2009

Are the datatypes in the database dates for strings?

Are you passing in the SQL as paramters or just SQL text?

Are you using any kind of date functions like createODBCdate(), etc.

Rodrigo Alarcon
Rodrigo AlarconAuthor
Known Participant
December 10, 2009

The fields in the database that correspond to dates are actually datetime type, not strings.

As you can see on the code (bellow this post's text), i'm not using any stored procedure for that so no params, but can eventually create a StoredProc to retrieve the info. Any way, those #dateFrom# and #dateTo# will be finally changed to <cfqueryparam cfsqltype="cf_sql_date" value="#dateFrom#"> and <cfqueryparam cfsqltype="cf_sql_date" value="#dateTo#">.

For your 3rd question, yes i'm using createODBCDateTime, actually i use just the date from the datepickers and add the time manually to ensure you can have a whole day of info.

<cfset dateFrom = CreateODBCDateTime(form.txt_fechaDesde & " 00:00:00")>
<cfset dateTo = CreateODBCDateTime(form.txt_fechaHasta & " 23:59:58")>

<cfif form.sel_vehiculo NEQ "">
    <cfquery name="qry_speed" datasource="#application.datasource#">   
        SELECT a.td_fechaEventoCL,
            td_speedKph,
            b.veh_id
        FROM tbl_tramaData a
        INNER JOIN tbl_asignaciones b on b.eq_imei = a.eq_imei
        WHERE a.td_fechaEventoCL >= #dateFrom#
        AND a.td_fechaEventoCL <= #dateTo#
        AND b.veh_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.sel_vehiculo#">
    </cfquery>
</cfif>

ilssac
ilssac
Inspiring
December 10, 2009

I'm not 100% sure what your question is?

You say the queries are working as desired?  But you are curious that the dates don't seem to be in the form you expect?

Computers don't look at dates like people do.  To a computer a date is actually a large integer that is the number of seconds after or before some arbitary date, Midnight January 1st 1900 for ColdFusion I believe, but different systems use a different starting point.

What you see, is just some arbitary string format to make that integer understandable to the average human, as very few of use could convert a large integer of seconds into anything meaningfull in our head.

You would probably have slightly better code if you used the <cfqueryparam value="#dateValue#" cfsqltype="cf_sql_datetime"> just like you are using for your integers.  But I don't know if this would change anything about what your question is.

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