Searching Between Two Dates

[FastJimmy]

Hi, I am creating an small application page in which I need to find birthdays of people in a contact table coming up in the next 7 days. I set the variables yesterday and week using dateAdd and Now(), which both become the respective correct dates. I then do a SQL query to find all contacts that have birthdays in the next 7. Besides the normal select and from statements I use:
WHERE #yesterday# < Birthday < #week#

The problem is no records are returned.

I believe that this is because the SQL code may equal this:
WHERE 6/20/2001 < 6/23/1975 < 6/28/2001
and no records may be coming up because the years do not match. I tried using DateFormat when I set the original variables for 'yesterday' and 'week' to display as: month/day, but when I try to use the DateFormat function to format the 'Birthday' in the SQL query I get an error.

Any help would be greatly appreciated!!! Thanks!!

Jimmy

 

[CFHub]

Try the BETWEEN operator...

WHERE Birthday BETWEEN #yesterday# AND #week#

(be sure these are all valid date objects and you are good to go)

 

[FastJimmy]

Hmm, I am still having the same problem after I tried this method out. Here's the code I am using if it will help anyone out:

Figure out yesterday
<cfset yesterday = '#dateAdd('y', -1, today)#'>
<cfset yesterday = #createODBCDate(yesterday)#>
Figure out 7 days from now
<cfset week = '#dateAdd('y', 7, today)#'>
<cfset week = #createODBCDate(week)#>


<cfquery name=&quot;getBirthdays&quot; datasource=&quot;#request.dsn#&quot;>
select contactID, nameFirst, nameLast, company, birthday from contacts

WHERE Birthday BETWEEN #yesterday# AND #week#
</cfquery>

Not sure where I am going wrong. Any help would be great!

Thanks!

Jimmy

 

[strantheman]

hi FastJimmy

mmmm ... I sure hope you've fixed this by now, but your problem is the format of the date for the SQL statement.

createODBCdate is great for inserts/ updates but not for filters. Use dateformat() and a standard date mask and surround the dates in quotes in your SQL and you will be fine.

Not sure what DB software you're using, but this should work:

Figure out yesterday
<cfset yesterday = '#dateAdd('y', -1, today)#'>
<cfset yesterday = #dateformat(yesterday,&quot;mm/dd/yyyy&quot#>
Figure out 7 days from now
<cfset week = '#dateAdd('y', 7, today)#'>
<cfset week = #dateformat(week,&quot;mm/dd/yyyy&quot#>


<cfquery name=&quot;getBirthdays&quot; datasource=&quot;#request.dsn#&quot;>
select contactID, nameFirst, nameLast, company, birthday from contacts

WHERE Birthday BETWEEN '#yesterday#' AND '#week#'
</cfquery>

 

[Guest_imported]

There's two problems here:

#1 How SQL Server is interpreting the ASCII dates you're feeding it.
#2 SQL Server being unable to do a binary comparison between the Datetime column you'd like and the beginning and end dates that you're feeding it in CF variables.

Important warning: I'm not at work right now, so I can't try the query I'm going to suggest. I'm going off my memory!

I would set your week and yesterday variables as strings. I would not use CreateODBC* to do it. Also, it depends on what locale your SQL Server is using whether or not it'll accept the dates you feed it. Because you're feeding it ASCII values that it needs to convert to a string, you may need to tweak your date formats to be sure that SQL Server is understanding it. See SET DATEFORMAT in the SQL Server Books Online.

Test how SQL Server is interpreting the dates you feed it by doing something like this:

DECLARE @curr_date VARCHAR(255)

SET @curr_date = '<the date you'd like to test>'

SELECT MONTH(@curr_date) AS Month_test

If it returns the correct number month, you're set. You can do the same with YEAR() and DAY() methinks. Also have a look at DATEPART().

If I need to pass a DATETIME as ASCII, I usually do something like:

<CFSET yesterday = '20010901 00:00:00'>
for September 1st, 2001, at midnight.

<cfquery name=&quot;getBirthdays&quot; datasource=&quot;#request.dsn#&quot;>
SELECT Contact_ID,
Birthday

FROM Contacts

WHERE CAST(Contacts.Birthday AS Binary) BETWEEN CAST('#yesterday#' AS Binary) AND CAST('#week#' AS Binary)

</cfquery>

CAST converts the Birthday column's result, and the yesterday and week variables into SQL Binary types, and then it does a simple less than and greater than comparison on them. It's little expensive in terms of query optimizer hints, but you do have lots of RAM & CPU capacity in the SQL Server, right?

Let us know how it goes.

 

[KeyserSoze1877]

SQL is picky when it comes to dates.

Cold Fusion's now() function is a tad different and SQL can be tricky and not accept it as a valid date. I use this technique all the time with no probs.

Try:

Where Birthday > #DateFormat(now(), &quot;mm/dd/yyyy&quot# and
Birthday < #DateFormat(DateAdd(now(), 7,&quot;d&quot, &quot;mm/dd/yyyy&quot#

 

[imstillatwork]

So what is the verdict here? I have had no luck at all with any of the suggestions.

the sql works, but it needs to work, regardless of the year.
all above only works in current year.

I would like som mor einfo on this also!

 

[calista]

How about if you format all your dates like this?

<cfset MyVar=DateCompare(#DateFormat(BirthDate,&quot;mmdd&quot#,#DateFormat(TodaysDate,&quot;mmdd&quot#)>
<cfoutput>#MyVar#</cfoutput>

BirthDate and TodaysDate are both OBDC dates, and if I use my birthdate (which happens to be November 19XX), and if I change the month to April, the DateCompare works as expected, so I think if you just format all your dates with month and day only, it will work. Calista :-X
Jedi Knight,
Champion of the Force