Skip to main content
S
straffenp
Known Participant
September 8, 2008
Question

Greater than or equal to

  • September 8, 2008
  • 5 replies
  • 1857 views
Hi everyone. I have what I think is a simple query (see below) that pulls date information from a table. As you can see from the query, I want it to pull dates for anything greater than and equal to today's date and anything less than and equal to a date one week from today's date.

This works, however, CF does not output any events on today's date; it begins outputting tomorrow's events. Can someone point me in the right direction as how to fix this so dates for today are output as well? Thanks!
    This topic has been closed for replies.

    5 replies

    Y
    yoman-B9oqA1
    Inspiring
    September 26, 2013

    Did you ever find an anwer to your question about "CF does not output any events on today's date; it begins outputting tomorrow's events." I've got the same issue. See recent post to Database Access.Thanks if you're there.

    S
    Steve Sommers
    Legend
    September 27, 2013

    For the "up through day" portion, I truncate the time component off of now(), add a day, and then use only the less than comparison. Example:

         <cfset variables.dt1 = dateAdd("d",1,int(now())) />

         <cfset variables.dt2 = dateAdd("d",-8,variables.dt1) />

         <p>dt1 = #variables.dt1# / dt2 = #variables.dt2#</p>

    This results into:

         dt1 = {ts '2013-09-28 00:00:00'} / dt2 = {ts '2013-09-20 00:00:00'}

    Lastly, use the results in your query comparison:

         where

              [someDateField] < <cfqueryparam value="#variables.dt1#" cfsqltype="CF_SQL_DATE" />

              and [someDateField] >= <cfqueryparam value="#variables.dt2#" cfsqltype="CF_SQL_DATE" />

    Also, the cfsqltype of CF_SQL_DATE seems to truncate the time component off the date for you in CF9 & CF10, but I don't like dependencies like that as I've been burned in the past. If you don't mind the risk, you can simplify the logic by removing the int() function from the above.

    Y
    yoman-B9oqA1
    Inspiring
    September 30, 2013

    So you created a workaround to avoid the LTE quirk. I suppose that's the mature thing to do, but I'm still at the point where I want things to work as they theoretically should. Haha. Thanks.

    -
    -__cfSearching__-
    Inspiring
    September 8, 2008
    straffenp wrote:
    > <cfset Today = #DateFormat(Timestamp, "mm/dd/yyyy")#>

    DateFormat returns a string, not a date time object. So it is possible the value is not being interpreted the way you think. Try converting "Today" to a datetime object.

    <cfset today = parseDateTime(dateFormat(now(), "yyyy-mm-dd"))>

    Also you should use cfqueryparam when passing query parameters.

    M
    Mi-ul-vmijiI
    Known Participant
    September 8, 2008
    This one has caught me out in the past!

    now() has a time component - I'm guessing all your events are manually entered dates without a time so an event today would be {ts 09-08-08 00:00:0 } and #Today# would be {ts 09-08-08 16:29:32} given a little leeway in the layout but you get the idea

    Mi-ul
    S
    straffenpAuthor
    Known Participant
    September 8, 2008
    Thanks for the advice everyone. The time issue totally makes sense. To try to fix it, I added another cfset:

    <cfset Timestamp = Now()>
    <cfset Today = #DateFormat(Timestamp, "mm/dd/yyyy")#>
    <cfset OneWeekFromToday = DateAdd("D",7,Today)>

    This is the output I get:
    Timestamp: {ts '2008-09-08 11:50:43'} | Today: 09/08/2008 | One Week From Today: {ts '2008-09-15 00:00:00'}

    The output of the dates stops at September 15 (one week from today), but now dates are being output as far back as the beginning August. Any thoughts?
    N
    Newsgroup_User
    Inspiring
    September 8, 2008
    if your DATE field includes a time part then pretty much any date you
    have entered into the db will be < NOW(). if so, you need to compare
    just the date parts. most db have date/time functions that will allow
    you to extract a specific part of a date stored in the field -check you
    db manual.

    hth

    Azadi Saryev
    Sabai-dee.com
    http://www.sabai-dee.com/
    P
    paross1
    Participating Frequently
    September 8, 2008
    Since you are setting Today as now(), it will contain a time component, so if your DATE column contains date/time values with no actual time component (default midnight, 00:00:00, etc.), then dates in your database from today will never be >= now(). You probably need to remove the time part of now() so that you are comparing today's date at midnight for both.

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