Find if A Date Is Between Two Dates

Question

mijorog 0 Newbie Poster

12 Years Ago

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!!!!!

coldfusion datecheck query

2 Contributors
18 Replies
1K Views
13 Hours Discussion Span
Latest Post 12 Years Ago Latest Post by arrgh

All 18 Replies

arrgh 22 Posting Whiz

12 Years Ago

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 you expect to happen?

Edited 12 Years Ago by arrgh because: n/a

arrgh 22 Posting Whiz

12 Years Ago

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?

arrgh 22 Posting Whiz

12 Years Ago

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 of all residents in the hospital on date X by eliminating the residentID = 193 clause. Then output the query results.

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

Edited 12 Years Ago by arrgh because: n/a

arrgh 22 Posting Whiz

12 Years Ago

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 <br>
selectedDate = <cfdump var="#selectedDate#">
<cfdump var="#checkResident#">

Edited 12 Years Ago by arrgh because: n/a

arrgh 22 Posting Whiz

12 Years Ago

<cfif #dateAdd("d", -1, checkResident.dayleft)

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 check all 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>

Edited 12 Years Ago by arrgh because: n/a

mijorog commented: Extremely Smart!! +1

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

mijorog 0 Newbie Poster · Answer 1 · 2011-08-30T23:56:34+00:00

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.

mijorog 0 Newbie Poster · Answer 2 · 2011-08-31T01:03:33+00:00

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

mijorog 0 Newbie Poster · Answer 3 · 2011-08-31T02:23:23+00:00

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

ie
<cfset selectedDate = "07/11/2011">
<cfquery name="checkResident" ...>
SELECT *
FROM hospital

WHERE residentID = 193
AND dayleft <= <cfqueryparam value="#selectedDate#" cfsqltype="cf_sql_timestamp">
AND dayreturned >= <cfqueryparam value="#selectedDate#" cfsqltype="cf_sql_timestamp">
</cfquery>

<cfif checkResident.recordCount gt 0>
<cfoutput>resident was in the hospital on #selectedDate#</cfoutput>
</cfif>

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.

mijorog 0 Newbie Poster · Answer 4 · 2011-08-31T02:53:06+00:00

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 0 Newbie Poster · Answer 5 · 2011-08-31T03:04:03+00:00

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 22 Posting Whiz · Answer 6 · 2011-08-31T03:08:04+00:00

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 22 Posting Whiz · Answer 7 · 2011-08-31T03:10:24+00:00

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 0 Newbie Poster · Answer 8 · 2011-08-31T03:15:38+00:00

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 22 Posting Whiz · Answer 9 · 2011-08-31T03:25:32+00:00

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 0 Newbie Poster · Answer 10 · 2011-08-31T03:44:30+00:00

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 ;-)

mijorog 0 Newbie Poster · Answer 11 · 2011-08-31T05:07:13+00:00

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 0 Newbie Poster · Answer 12 · 2011-08-31T05:16:11+00:00

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 22 Posting Whiz · Answer 13 · 2011-08-31T06:08:45+00:00

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 
    )

Find if A Date Is Between Two Dates

Recommended Answers Collapse Answers

All 18 Replies

Recommended Answers