Make sure the date is in the correct format

Report · Dec 09, 2009

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ículo:
            </td>
            <td>
                <cfselect name="sel_vehiculo"
                    query="qry_vehiculos"
                    display="veh_info"
                    value="veh_id"
                    queryPosition="below">
                    <option value="">Seleccione un Vehí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>

Report · Dec 09, 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.

Report · Dec 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.

<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>

Report · Dec 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.

Report · Dec 10, 2009

Yes, actually i knew that stuff about dates are just a way to represent numbers that computers will understan, but for some odd reason this is what happening.

I have a date picker which will show you the date in dd/mm/yyyy format, that date picker is done using jQuery library. When u submit the form the date is still untouched (same format as the date picker), when it's time to use that date to filter a query i can see that coldfusion swaps the day and the month when, if they are swapped, can generate a valid date but when i pick up days that can't be months (any day over 12) it will stay as i need it. Just to be more specific i'm dropping an example here:

Date from Datepicker (dd/mm/yyyy)	After posting the form (dd/mm/yyyy)	After createODBCDateTime (yyyy-mm-dd hh:mm:ss)
15/12/2009	15/12/2009	{ts '2009-12-15 17:25:32'}
02/03/2009	02/03/2009	{ts '2009-02-03 17:25:32'}

I hope this time it's more clear so i can have some tips on how to force Coldfusion to use the date as i need it.

Report · Dec 10, 2009

I see, you may need to be using some form of the LS date fucntions or similar to let ColdFusion know what form your dates are in,

Report · Dec 10, 2009

YourDateVariable = CreateDate(listlast, form.date, "/"

, ListGetAt(form.date, 2, "/")

, ListFirst(form.date, "/");

Report · Dec 10, 2009

Finally managed to fix this with this forced conversion:

And execute the select as:

<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 BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#temp_txt_dateFrom#">

        AND <cfqueryparam cfsqltype="cf_sql_date" value="#temp_txt_dateTo#">
        AND b.veh_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.sel_vehiculo#">
    </cfquery>
</cfif>

Dan's reply was kinda usefull so i rated it as that, as well as Ian's!

Much thnx for the time and help

Report · Dec 10, 2009

Rodrigo Alarcon wrote:

WHERE a.td_fechaEventoCL BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#temp_txt_dateFrom#">
AND <cfqueryparam cfsqltype="cf_sql_date" value="#temp_txt_dateTo#">

I'm glad you got it to work. But you may care to know that by using the "cf_sql_date" type, as apposed to the "cf_sql_timestamp" format, these query param statements are stripping out the time parts of you date time variables you worked so hard to add.