Skip to main content
T
trojnfn
Inspiring
April 20, 2006
Question

Date Problem

  • April 20, 2006
  • 5 replies
  • 545 views
I define todays date with
<cfset todays_date=dateformat(now(),"mm/dd/yyyy")>

I then do a sql insert into a field called date_submitted into an access table, with date_submitted defined as a long date.

I then want to report all records submitted each day, using
<cfquery name="qryName" datasource="db_name">
sele.ct * from tblName
where date_submitted = #todays_date#
</cfquery>

No records are found. I remove the where clause and display the dates only and for date_submitted, it is 19-Apr-06 and todays_date is 04/19/2006. I redefine todays_date with dd-mmm-yy and still no records are found. I know there are records that match. But I do not know what is going on.

To accomplish what I want, I just select all records from the table regardless of date and put all records into a temp table, then do the where clause again, and it works. I do not know why it works this way and not with the table itself.

Please help, any ideas on what to do ? thanks
    This topic has been closed for replies.

    5 replies

    A
    Anonymous
    April 20, 2006
    You should use the URLEncodedFormat( ) function when passing variables with certain characters in them. Forward-slash is one of them.
    D
    Dan_Bracuk
    Inspiring
    April 20, 2006
    Pass the variable without the odbc function.
    D
    Dan_Bracuk
    Inspiring
    April 20, 2006
    Most db's have a time component with date fields. If the time component is not specified, it defaults to 00:00, the start of the day. So what you have to is replace:
    where date_submitted = #todays_date#
    with
    where date_submitted >= #todays_date# and date_submitted < (whatever access's syntax is for adding one day to the todays_date)


    N
    Newsgroup_User
    Inspiring
    April 20, 2006
    The ScareCrow wrote:
    > If you only want to store the date use createodbcdate(now()) to insert
    > if you want the date and time use createodbcdatetime(now())


    <cfif createodbcdatetime(now()) EQ now()>
    urban myth
    <cfelse>
    not an urban myth
    </cfif>
    T
    The_ScareCrow
    Inspiring
    April 20, 2006
    As Paul has said, it is an urban myth.

    But

    I feel that by using the createodbc functions it keeps all sql statements consistant and the reader of the code knows what is being passed to the RDMS.

    Also notice that it is only createodbcdatetime that is the same as now.

    Ken
    T
    trojnfnAuthor
    Inspiring
    April 20, 2006
    OK, I got it to work using date_submitted = #createodbcdatetime(todays_date)#

    Now the next question is : I calculate other dates using todays_date (Now () ) like :
    <cfset todays_date = dateformat(now(),"mm/dd/yyyy")>
    <cfset next_date = dateformat(DateAdd("d", 1, now()),"mm/dd/yyyy")>
    <cfset yesterdays_date_1 = dateformat(DateAdd("d", -1, now()),"mm/dd/yyyy")>
    <cfset yesterdays_date_2 = dateformat(DateAdd("d", -2, now()),"mm/dd/yyyy")>

    I want to pass one of these dates, like yesterdays_date_1 as a url variable like this :
    <a href="../../../mmi_form_queue.cfm?date_submitted=#createodbcdatetime(yesterdays_date_1)#">#yesterdays_date_1#</a>

    Now this does not work because it seems that it does not like the createodbcdatetime, so no records are being found. How can I get this to work ?

    Not only do I want to get records daily, I also want to have links that go back seven days to get records for any one of those days.
    T
    The_ScareCrow
    Inspiring
    April 20, 2006
    If you only want to store the date use createodbcdate(now()) to insert
    if you want the date and time use createodbcdatetime(now())

    In the query use
    where date_submitted = #createodbcdate(todays_date)#

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