-
Query - with date in where clause
Hi,t
I'm trying to run a query to return values from the 10th of last month to the 9th of this month, whenever it is ran. I tried add_months but it is giving me records from a month ago e.g.
select name,dept,age
from mytable
where hire_date >= add_months(sysdate,-1)
I need the query to return value from 10th of last month to 9th of this month no matter when it is ran.
Thanks
OCP 8i DBA
I'm still learning
-
romeo@oradev>select
2 to_char(add_months(trunc(sysdate,'MONTH'),-1)+9,'MM/DD/YYYY') last_month,
3 to_char(add_months(trunc(sysdate,'MONTH'),0)+8,'MM/DD/YYYY') this_month
4 from dual
5 ;
LAST_MONTH THIS_MONTH
---------- ----------
05/10/2004 06/09/2004
romeo@oradev>
OCP DBA 8, 8i, 9i, 10g
-
Thx Romeo, I will try this.
OCP 8i DBA
I'm still learning
-
You should try:
select last_day(add_months(sysdate,-2))+10,last_day(add_months(sysdate,-1))+9 from dual;
-
Thx for your suggestions guys. What I'm really trying to do is to allow my query to get values between 10th of last month and 9th of this month whenever the query is ran. It is not just simply select those dates.
Anyway thanks
OCP 8i DBA
I'm still learning
-
select name,dept,age
from mytable
where hire_date between add_months(trunc(sysdate,'MONTH'),-1)+9 and
add_months(trunc(sysdate,'MONTH'),0)+8
;
OCP DBA 8, 8i, 9i, 10g
-
Thanks Romeo, I was able to use to achieve the task by specifying the mon/year at run time
e.g. @c:\mytest2.sql mar/2004
with the where clase below:
between add_months(to_date('16/&1','dd/mon/yyyy'),-1) and
add_months(to_date('16/&1','dd/mon/yyyy'),+0)-1
-- &1 will be mar/2004
Thanks a lot
OCP 8i DBA
I'm still learning
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|