Home » SQL & PL/SQL » SQL & PL/SQL » Date between issue (oralce 8i)
Date between issue [message #393916] Tue, 24 March 2009 22:03 Go to next message
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 #393917 is a reply to message #393916] Tue, 24 March 2009 22:19 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/m/380433/136107/#msg_380433

>'01-Jan-2009' and '31-Jan-2009'
With Oracle, STRING data types have single quote marks at each end.
Above are STRINGS not DATE data types.


>But, whenever I pass the attd date from
PASS? Everything you posted is hardcoded.

Re: Date between issue [message #393984 is a reply to message #393916] Wed, 25 March 2009 04:59 Go to previous messageGo to next message
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 #393987 is a reply to message #393984] Wed, 25 March 2009 05:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you trunc to month what is the need of between?

In addition, '01-Jan-2009' and '31-Jan-2009' are strings not dates.

Regards
Michel
Re: Date between issue [message #394033 is a reply to message #393984] Wed, 25 March 2009 08:10 Go to previous message
joy_division
Messages: 4641
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

Previous Topic: How to spool CLOB Column's data to file?
Next Topic: Copy the image file to a folder
Goto Forum:
  


Current Time: Wed Dec 07 05:12:04 CST 2016

Total time taken to generate the page: 0.06391 seconds