| Date between issue [message #393916] |
Tue, 24 March 2009 22:03  |
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
|
Member |
|
|
Hi,
Pls take a look of the below query,
select a.attd_d attd_date , b.lv_c code from admin_emp_attd a, admin_attd_lv_detl b
where a.attd_d between '01-Jan-2009' and '31-Jan-2009'
and a.attd_seq_n = b.attd_seq_n
and lv_c not in ('OFF')
and emp_n = 1245;
The output is shown below ...
SQL> /
ATTD_DATE COD
---------- ---
01-01-2009 HOL
02-01-2009 LWP
19-01-2009 SLV
20-01-2009 SLV
21-01-2009 SLV
26-01-2009 HOL
27-01-2009 HOL
But, whenever I pass the attd date from
1. '15-Jan-2009 to '31-Jan-2009 (or)
2. '01-Jan-2009 to '15-Jna-2009 (or)
3. '12-jan-2009 to '19-jan-2009 , then it should display the above result
How this can be achieved ? Thanks.
-Karthik
-Karthik
|
|
|
|
|
|
| Re: Date between issue [message #393984 is a reply to message #393916] |
Wed, 25 March 2009 04:59  |
ashreddy
Messages: 19
Registered: March 2009
|
Junior Member |
|
|
You can round off the date column value to the month so that all the records existing in that particular month can be fetched irrespective of the input params bewtween dates..
i.e.
where TRUNC(a.attd_d,'MON') between '01-Jan-2009' and '31-Jan-2009'
|
|
|
|
|
|
| Re: Date between issue [message #394033 is a reply to message #393984] |
Wed, 25 March 2009 08:10 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
| ashreddy wrote on Wed, 25 March 2009 05:59 | You can round off the date column value to the month so that all the records existing in that particular month can be fetched irrespective of the input params bewtween dates..
i.e.
where TRUNC(a.attd_d,'MON') between '01-Jan-2009' and '31-Jan-2009'
|
You are just exacerbating the problem by promoting incorrect use of DATEs and strings.
FOO SCOTT>select 1 from dual
2 where sysdate between '01-Jan-2009' and '31-Jan-2009'
3 /
where sysdate between '01-Jan-2009' and '31-Jan-2009'
*
ERROR at line 2:
ORA-01843: not a valid month
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
