Greater than or equal to

[straffenp]

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!

[paross1]

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

[Newsgroup_User]

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/

[Mi-ul-vmijiI]

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

[straffenp]

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?

[-__cfSearching__-]

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.

[yoman-B9oqA1]

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.

[Steve Sommers]

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.

[yoman-B9oqA1]

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.