Discuss Mysql between dates in the Delphi Programming forum on Dev Shed. Mysql between dates Delphi Programming forum discussing Delphi related topics including Kylix, C++ Builder, and more. Delphi is a high-performance language, originally based on the PASCAL language.
Posts: 165
Time spent in forums: 1 Day 4 h 42 m 59 sec
Reputation Power: 36
Mysql between dates
How do i run a query for between dates?
to do ONE date for between is easy i know. but what if i have 2 dates?
i have a leave program. let say i take of from the 10th to the 15th. I want to run a query from the 7th to the 17th and then that record should appear.
It is just as if i am telling you that range 12-15 is between 10 and 20.
Posts: 255
Time spent in forums: 3 Days 2 m 12 sec
Reputation Power: 5
I think what you want is to capture if either the from_date or the to_date are within your range,
select from_date, to_date from Table T
where
(T.From_Date between :startdate and :enddate) or
(T.To_Date between :startdate and :enddate)
use the query's parambyname property to set the startdate and enddate parameters.
This will include the record if either date is between the range, so if someone starts their leave on the end date, or if the leave ends on the start date, or if the leave is completely within the period, it will be selected.
Posts: 2,045
Time spent in forums: 1 Week 6 Days 2 h 37 m
Reputation Power: 382
Quote:
Originally Posted by majlumbo
I think what you want is to capture if either the from_date or the to_date are within your range,
select from_date, to_date from Table T
where
(T.From_Date between :startdate and :enddate) or
(T.To_Date between :startdate and :enddate)
I agree that this looks correct.
My second suggestion should have used an OR not AND and would give the same
result I think; but this is a much clearer expression of the intent of the query
and therefore superior.
Posts: 255
Time spent in forums: 3 Days 2 m 12 sec
Reputation Power: 5
Quote:
Originally Posted by clivew
I agree that this looks correct.
My second suggestion should have used an OR not AND and would give the same
result I think; but this is a much clearer expression of the intent of the query
and therefore superior.
Clive
Actually, my query does not solve the problem. If someone starts their leave before the start date, and ends it after the end date, then it wouldn't be selected even thought they were on leave during the period.
This should work however...
Code:
select from_date, to_date from Table T
where
(T.From_Date between :startdate and :enddate) or
(T.To_Date between :startdate and :enddate) or
(T.From_Date < :startdate and T.To_Date > :enddate)
Last edited by majlumbo : July 12th, 2012 at 04:29 PM.
Posts: 165
Time spent in forums: 1 Day 4 h 42 m 59 sec
Reputation Power: 36
re
Thanks guys. i do not know why i did not think of trying 2 between statements. but i would not have think of the last statement. appreciate all your help.
Posts: 2,045
Time spent in forums: 1 Week 6 Days 2 h 37 m
Reputation Power: 382
Our pleasure.
I have been thinking about my simple query and I am not sure that it is wrong.
If you have time to test it against majlumbo's query to see if it yields the same results
or anyone else can explain where it would fail I would be grateful.
Here it is again (using parameters this time)
Code:
SELECT * FROM TABLE T
WHERE T.FROM_DATE <= :enddate
AND T.TO_DATE >= :startdate
Posts: 165
Time spent in forums: 1 Day 4 h 42 m 59 sec
Reputation Power: 36
Re
Hallo Clive
Your query might not work if i start my leave after the fromdate?
lets say i take leave from the 19th to the 21th.
I create a report from the 17th to the 25. To see whom in that period take leave...i would not be on the list... my STARTDATE is AFTER the :fromdate but my ENDDATE is smaller than my :todate.
am i right in saying that? Then majlumbo's code will work?
Posts: 2,045
Time spent in forums: 1 Week 6 Days 2 h 37 m
Reputation Power: 382
Quote:
Your query might not work if i start my leave after the fromdate?
lets say i take leave from the 19th to the 21th.
I create a report from the 17th to the 25.
SQL Code:
Original
- SQL Code
SELECT * FROMTABLE T
WHERE T.FROM_DATE <= :enddate
AND T.TO_DATE >= :startdate
Yes it will.
Taking your dates:
T.FROM_DATE is 19th.
T.TO_DATE is 21st.
:enddate is 25th.
:startdate is 17th.
19 is less than 25
21 is greater than 17
Therefore both conditions are true and the record will be included.
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
