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>

Rodrigo Alarcon
Rodrigo AlarconAuthor
Known Participant
December 10, 2009

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

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

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


Finally managed to fix this with this forced conversion:

<cfset temp_txt_dateFrom= LSDateFormat(form.txt_dateFrom,"yyyy-mm-dd") & " 00:00:00">
<cfset temp_txt_dateTo= LSDateFormat(form.txt_dateTo,"yyyy-mm-dd") & " 23:59:59">

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

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