Find From and To Date between Two fields

[kartheeee]

Hi, I have oracle table for Leave

LeaveNo FromDt ToDt
1 01/01/2010 31/03/2010
2 01/02/2010 31/05/2010
3 01/04/2010 30/04/2010
4 25/04/2010 15/05/2010
5 01/05/2010 31/05/2010

I want find out who was leave on 01/04/2010 to 30/04/2010.
how to write oracle sql?

tks in advance
kartheeee

[anubina]

Hi, I have oracle table for Leave

LeaveNo FromDt ToDt
1 01/01/2010 31/03/2010
2 01/02/2010 31/05/2010
3 01/04/2010 30/04/2010
4 25/04/2010 15/05/2010
5 01/05/2010 31/05/2010

I want find out who was leave on 01/04/2010 to 30/04/2010.
how to write oracle sql?

tks in advance
kartheeee

-- With hour
SELECT lev.LeaveNo
  FROM t_leave lev
 WHERE TO_DATE('01/04/2010 00:00:00','DD/MM/YYYY hh24:mi:ss') >= lev.FromDt
   AND TO_DATE('30/04/2010 00:00:00','DD/MM/YYYY hh24:mi:ss') <= lev.ToDt
   AND lev.FromDt <= lev.ToDt -- Check that the LeaveDt is later at the FromDt

-- Without hour
SELECT lev.LeaveNo
  FROM t_leave lev
 WHERE TRUNC(TO_DATE('01/04/2010 00:00:00','DD/MM/YYYY hh24:mi:ss')) >= TRUNC(lev.FromDt)
   AND TRUNC(TO_DATE('30/04/2010 00:00:00','DD/MM/YYYY hh24:mi:ss')) <= TRUNC(lev.ToDt)
   AND lev.FromDt <= lev.ToDt -- Check that the LeaveDt is later at the FromDt

Remember: You can inyect your FromDt & LevDt parameters into textfire '01/04/2010 00:00:00' & '30/04/2010 00:00:00'. If your parameters is an date object, you must omit cast TO_DATE('xxx','xxx')

[kartheeee]

Hi anubina,
thks for immediate reply.

but this query is not retrieveing the data like this
6 - 15/03/2010 - 15/04/2010
7 - 01/03/2010 - 29/04/2010
8 - 16/04/2010 - 10/05/2010

-- With hour
SELECT lev.LeaveNo
  FROM t_leave lev
 WHERE TO_DATE('01/04/2010 00:00:00','DD/MM/YYYY hh24:mi:ss') >= lev.FromDt
   AND TO_DATE('30/04/2010 00:00:00','DD/MM/YYYY hh24:mi:ss') <= lev.ToDt
   AND lev.FromDt <= lev.ToDt -- Check that the LeaveDt is later at the FromDt

-- Without hour
SELECT lev.LeaveNo
  FROM t_leave lev
 WHERE TRUNC(TO_DATE('01/04/2010 00:00:00','DD/MM/YYYY hh24:mi:ss')) >= TRUNC(lev.FromDt)
   AND TRUNC(TO_DATE('30/04/2010 00:00:00','DD/MM/YYYY hh24:mi:ss')) <= TRUNC(lev.ToDt)
   AND lev.FromDt <= lev.ToDt -- Check that the LeaveDt is later at the FromDt

Remember: You can inyect your FromDt & LevDt parameters into textfire '01/04/2010 00:00:00' & '30/04/2010 00:00:00'. If your parameters is an date object, you must omit cast TO_DATE('xxx','xxx')

[anubina]

Hi anubina,
thks for immediate reply.

but this query is not retrieveing the data like this
6 - 15/03/2010 - 15/04/2010
7 - 01/03/2010 - 29/04/2010
8 - 16/04/2010 - 10/05/2010

Sorry, I now understand you

SELECT lev.LeaveNo
  FROM t_leave lev
 WHERE lev.FromDt BETWEEN TO_DATE('01/04/2010 00:00:00','DD/MM/YYYY hh24:mi:ss')
                      AND TO_DATE('30/04/2010 00:00:00','DD/MM/YYYY hh24:mi:ss')

P.S. My level english is low

[kartheeee]

Sorry, in this query u r not considered the todate.
u had checked only from date starting from april month.

but if a person taken leave before april month na, this will not retrieve.
6 - from:01/03/2010 - to:15/04/2010
or
7 - from:01/03/2010 - to:31/05/2010

tks for ur reply
kartheeee

Sorry, I now understand you

SELECT lev.LeaveNo
  FROM t_leave lev
 WHERE lev.FromDt BETWEEN TO_DATE('01/04/2010 00:00:00','DD/MM/YYYY hh24:mi:ss')
                      AND TO_DATE('30/04/2010 00:00:00','DD/MM/YYYY hh24:mi:ss')

P.S. My level english is low

[tesuji]

Hi

if you are only interested in start and end dates of leaves of a given month, you may check that month whether it is contained in those dates, as for example:

select To_Char(fromdt,'MM') as from_month, To_Char(todt,'MM') as to_month, leaveno 
from onleave ... etc ...

If you like to get shorts for months, as for 'Jan', 'Feb' etc, you may replace MM by MON.

There are comprehensive manuals for Oracle DB, so you might examine date and time functions there.

-- tesu

[kartheeee]

User may search with different parameter,
like 1/4/2010 to 10/04/2010
5/4/2010 to 5/4/2010

tks very much

Hi

if you are only interested in start and end dates of leaves of a given month, you may check that month whether it is contained in those dates, as for example:

select To_Char(fromdt,'MM') as from_month, To_Char(todt,'MM') as to_month, leaveno 
from onleave ... etc ...

If you like to get shorts for months, as for 'Jan', 'Feb' etc, you may replace MM by MON.

There are comprehensive manuals for Oracle DB, so you might examine date and time functions there.

-- tesu

[tesuji]

It doesn´t matter. Variable start- and end-months are covered by the select I posted. You only need to consider that in the where clause by an OR condition, for example:

... ((from_month=@fmonth) OR (to_month=@tmonth)) order by to_month, leaveno ;

Where @fmonth and @tmonth are the month-values extracted from the dates the user typed in and syntactically depending on the environment this select will be carried out. You can also compose such a select by simple string functions.

Btw, If you add further AND-terms to above where clause, parenthesises as shown are mandatory because of operator precedence.

-- tesu

[yash_792]

Hi try this...

select leaveno,fromdt,todt
from leavetb
where fromdt >= to_date('01-Apr-2010')
and todt <= to_date('30-Apr-2010');

[NeverLift]

The question is ambiguous, please clarify:

Do you want all whose leave period totally encompasses the desired period, or all whose leave period includes any part (including just one day) of the desired period? I have an algorithm (about 30 years old ;) ) I created for the latter, a "range of range" search. I'd have to recast it in SQL.