Find if A Date Is Between Two Dates

[mijorog]

So I have a database that stores when someone leaves and when they return to.
dayleft
dayreturned

I need to do a query and also an cfif to see if a date selected is between these dates.
test - selected date

the
Here is my long if

<cfif (datecompare(vactrue.dayreturned, test, "yyyy") eq 0 AND datecompare(vactrue.dayreturned, test, "m") eq 0 AND datecompare(vactrue.dayreturned, test, "d") eq 0) 
OR (datecompare(vactrue.dayreturned, test, "yyyy") eq -1)      
OR (datecompare(vactrue.dayreturned, test, "yyyy") eq 0 AND datecompare(vactrue.dayreturned, test, "m") eq 0 AND datecompare(vactrue.dayreturned, test, "d") eq -1)
OR (datecompare(vactrue.dayreturned, test, "yyyy") eq 0 AND datecompare(vactrue.dayreturned, test, "m") eq -1 AND datecompare(vactrue.dayreturned, test, "d") eq -1)
OR (datecompare(vactrue.dayreturned, test, "yyyy") eq 0 AND datecompare(vactrue.dayreturned, test, "m") eq -1 AND datecompare(vactrue.dayreturned, test, "d") eq 0)
OR (datecompare(vactrue.dayreturned, test, "yyyy") eq 0 AND datecompare(vactrue.dayreturned, test, "m") eq -1 AND datecompare(vactrue.dayreturned, test, "d") eq 1)> 

<cfif (datecompare(vactrue.dayleft, test, "yyyy") eq 0 AND datecompare(vactrue.dayleft, test, "m") eq 0 AND datecompare(vactrue.dayleft, test, "d") eq 0) 
OR (datecompare(vactrue.dayleft, test, "yyyy") eq 1)      
OR (datecompare(vactrue.dayleft, test, "yyyy") eq 0 AND datecompare(vactrue.dayleft, test, "m") eq 0 AND datecompare(vactrue.dayleft, test, "d") eq 1)
OR (datecompare(vactrue.dayleft, test, "yyyy") eq 0 AND datecompare(vactrue.dayleft, test, "m") eq 1 AND datecompare(vactrue.dayleft, test, "d") eq 1)
OR (datecompare(vactrue.dayleft, test, "yyyy") eq 0 AND datecompare(vactrue.dayleft, test, "m") eq 1 AND datecompare(vactrue.dayleft, test, "d") eq 0)
OR (datecompare(vactrue.dayleft, test, "yyyy") eq 0 AND datecompare(vactrue.dayleft, test, "m") eq 1 AND datecompare(vactrue.dayleft, test, "d") eq -1)>

Here is my query

<cfquery datasource="residents5" name="hosptrue">
          SELECT *
          FROM hospital
          WHERE ID = 193 AND dayreturned IS NOT NULL 
          AND (DATEDIFF(dayleft, #test#) >= 0 AND DATEDIFF(#test#, dayreturned) <= 0)
          </cfquery>

Neither of these seemed to work and was hoping that someone could enlighten me to my errors. Thanks!!!!!

[arrgh]

It's not going to be nearly that complicated.

What's your query used for in plain english? ie Is it a reservation system and you're trying to find out what's booked?Neither of these seemed to work

Can you give an an example of all 3 dates and what youexpect to happen?

[mijorog]

I work for an assisted living facility company. We have have a report that shows each day when and who gave their medication. If they arent given their meds we need to indicate why. that being whether a nurse forgot or they were in the hospital or on vacation. When a resident goes on vacation/hospital it is entered into a DB when they left and when they returned. I need to pull that information from that database and check if whatever date we are looking at is between those dates.

I hope that makes sense.

[arrgh]

I think so. Can you also post a few examples? Those are better than a 1000 words of description. Something like this

theSelectedDate => 08/30/2011

Sample Data
recordID, dayLeft, dayreturned,
#1, 08/01/2011, NULL
#2, 08/10/2011, 08/16/2011
#3, 08/28/2011, 09/05/2011
....

Which records should the query return and why?

[mijorog]

Table = hosptial
Feilds:
ID, residentID, dayleft, dayreturned
#1, 193, 08/01/2011, NULL
#2, 16, 08/10/2011, 08/16/2011
#3, 47, 08/28/2011, 09/05/2011
#4, 193, 07/10/2011, 07/13/2011

selected date = 07/11/2011
residentID = 193
I need find all the records for resident 193 in hospital database and see if my selected date of 07/11/2011 is in between any date ranges in the database. So for this it is in between ID #4.

If it is in between any of the date ranges I need it to output that the resident was in the hostpital.

Sorry about not putting this sooner. I missed it when you asked he first time

[arrgh]

Then all you'd need is this query.

ie
<cfset selectedDate = "07/11/2011">

<cfquery name="checkResident" ...>
SELECT *
FROM   hospital
<!--- be sure to use cfqueryparam if resident id is a variable --->
WHERE  residentID = 193
AND    dayleft <= <cfqueryparam value="#selectedDate#" cfsqltype="cf_sql_timestamp">
AND    dayreturned >= <cfqueryparam value="#selectedDate#" cfsqltype="cf_sql_timestamp">
</cfquery>

<!--- if a matching record was found .. --->
<cfif checkResident.recordCount gt 0>
  <cfoutput>resident was in the hospital on #selectedDate#</cfoutput>
</cfif>

Or you could generate a list ofall residents in the hospital on date X by eliminating the residentID = 193 clause. Then output the query results.

<cfoutput query="checkResident">
    ... show names ...
</cfoutput>

[mijorog]

I ran this one and nothing worked(no output on dates I know they were in the hospital).

ie SELECT * FROM hospital WHERE residentID = 193 AND dayleft <= AND dayreturned >= resident was in the hospital on #selectedDate#

I changed cfsqltype="cf_sql_timestamp" to cfsqltype="cf_sql_date". Doing this now it pertially works. It will not output if the selectedDate = dayleft. Not sure exactly why this is happening.

Any Ideas?

The report this is for single residents so doing the output query you mentioned wont work in this scenario.

[arrgh]

I changed cfsqltype="cf_sql_timestamp" to cfsqltype="cf_sql_date"

Shouldn't make a difference. Not unless you're storing times too, not just dates. Are you? Also, what db type?It will not output if the selectedDate = dayleft.
Let's do some debugging. Grab all records for resident #193. Can you dump the dates only. Then post the results. So we can see why they're not matching?

<cfquery name="checkResident" ...>
SELECT dayleft , dayreturned 
FROM hospital
WHERE residentID = 193
</cfquery>

Debugging stuff 
selectedDate = <cfdump var="#selectedDate#">
<cfdump var="#checkResident#">

[mijorog]

ok I think that I got it, but it feels like cheating. I moved the where clause you gave me into a cfif statement

<cfif #dateAdd("d", -1, checkResident.dayleft)# LTE #createODBCDate(selectedDate)# AND #createODBCDate(checkResident.dayreturned)# GTE #createODBCDate(selectedDate)#>

You can see that I did a dateAdd to minus one day from the dateleft to fix the problem that it wouldn't recognize when the dayleft and selectedDate were the same.

I am gonna leave this unsolved for about 30 minutes just in case you have another idea besides that. After 30 minutes and if I don't see anything from you arrgh, I will mark it a solved.

you have helped me out greatly. Thank you!!!!!!!!:)

[mijorog]

We are storing date/times but not using the times for this report. This is a MYSQL DB

RESULTSET	
query
 	DAYreturned	DAYleft
1	2011-01-18 11:07:26.0	2010-12-10 12:01:13.0
2	2011-08-12 15:30:18.0	2011-08-10 15:17:47.0
3	[empty string]	2011-08-19 16:23:34.0
CACHED	false
EXECUTIONTIME	0
SQL	SELECT dayleft , dayreturned FROM hospital WHERE ID = 193

[arrgh]

Since it's not trivial stuff, I'd rather recommend the right code. But without the debugging output, I don't know if what you posted is right or not ;-) If you can post it, I can tell you what went wrong and how to fix it. Correctly.

[arrgh]

Oops... our responses keep overlapping. lol

We are storing date/times but not using the times for this report. This is a MYSQL DB

Yep. That explains it :) What #selectedDate# did you actually use in your test? Still "07/11/2011"?

[mijorog]

This is what I am working with right now.
selectedDate = 2011-08-10
It matches up with DayLeft but will show that the resident is Not in the hospital.
That is why I did the date add to minus a day and checked it against other residents and it works. I dont think this is the correct way to do it but it is working.

<cfset Adate = createODBCDate(NOW())>
<cfset selectedDate = dateAdd("d", -20, Adate)> 
<cfset selectedDate = #createODBCDate(selectedDate)#>

<cfquery name="checkResident" datasource="residents5">
SELECT *
FROM   hospital
<!--- be sure to use cfqueryparam if resident id is a variable --->
WHERE  ID = 193
<!---AND    dayout =< <cfqueryparam value="#selectedDate#" cfsqltype="cf_sql_date">
AND    dayin >= <cfqueryparam value="#selectedDate#" cfsqltype="cf_sql_date">
--->
</cfquery>

<cfif checkResident.recordCount gt 0>

<cfif #dateAdd("d", -1, checkResident.dayleft)# LTE #createODBCDate(selectedDate)# AND #createODBCDate(checkResident.dayreturned)# GTE #createODBCDate(selectedDate)#>
  <cfoutput>resident was in the hospital on #selectedDate#</cfoutput>
  <cfelse>
  <cfoutput>resident was NOT in the hospital on #selectedDate#</cfoutput>
</cfif>       
</cfif>

[arrgh]

Oh, no. Don't use that code. If it works, it's pure luck ;-)

What's this "dayout" and "dayin" value? Are you using those columns or just DAYreturned and DAYleft?

[mijorog]

It is just dayleft and day returned. They used to be dayout and dayin but it was rather confusing, I didnt set up the database originally. I had to change the around so people understood what was in them.

I am confuse by what you mean with
Oh, no. Don't use that code. If it works, it's pure luck ;-)

[arrgh]

That only checks whatever dates happen to be in the 1st record of the query. Remember you removed the WHERE clause. So there might be more than 1 record returned. For accurate results you'd have to cfloop through the query to checkall of them.

The original sql didn't work because I didn't know the data contained times too. So it was the wrong the comparison. Just adjust it to account for the time.

Example: if today is 8/30/2011, #selectedDate# will be 8/10/2011 and #dayAfter# will be 8/11/2011.

<cfset todaysDate = createODBCDate(NOW())>
<cfset selectedDate = dateAdd("d", -20, todaysDate)> 
<cfset dayAfter     = dateAdd("d", 1, selectedDate)>

<cfquery name="checkResident" datasource="residents5">
SELECT *
FROM hospital
<!--- be sure to use cfqueryparam if resident id is a variable --->
WHERE ID = 193
AND   dayLeft    < <cfqueryparam value="#dayAfter#" cfsqltype="cf_sql_date">
AND   dayReturn > <cfqueryparam value="#selectedDate#" cfsqltype="cf_sql_date">
</cfquery>

[mijorog]

You my friend are a genius!!!

I have one more question. What should I do if the dayreturned is NULL. Like from the cfdump we did that the resident is currently in the hospital. It is not showing that the resident is in the hospital.

[mijorog]

Ok so I did this It seems to be working

<cfquery name="checkResident2" datasource="residents5">
SELECT *
FROM hospital
<!--- be sure to use cfqueryparam if resident id is a variable --->
WHERE ID = 193
and dayreturned IS NULL
AND   dayleft  < <cfqueryparam value="#dayAfter#" cfsqltype="cf_sql_date">
</cfquery>

I just ran a second query looking for dayreturned IS NULL and kept the dayleft where statement. It looks right. Again arrgh I cant thank you enough!!!!!

[arrgh]

I just ran a second query looking for dayreturned IS NULL and kept the dayleft where statement

Yep, that'll do it! Good job.

If you want everything in 1 query. Just do somethin like this.

WHERE ID = 193 
AND dayLeft < <cfqueryparam value="#dayAfter#" cfsqltype="cf_sql_date">
AND ( <!--- using greater than or equals to also include selectedDate at midnight exactly --->
    dayReturn >= <cfqueryparam value="#selectedDate#" cfsqltype="cf_sql_date"> 
OR  dayReturn IS NULL 
    )