-
add_months
Hi,
I have requirement to check one month back of sysdate to see if hiredate is less then the sysdate. In this where clause I have to use format MON-YYYY on both side client does not want to check with DD-MON-YYYY. How I can check this only month & year? Please see below e.g
where to_char(trunc(hire_date),'MON-YYYY') < add_months(trunc(sysdate),-1)
* The left side of the condition is fine, but don't know how to use MON-YYYY on the right side.
Thanks in advance.
-
If what you want is all records where the hire date is more than one month old, then ...
Code:
where hire_date < add_months(trunc(sysdate),-1)
Otherwise, please explain more clearly what the requirement is.
-
PHP Code:
1 select sysdate, to_char(add_months(sysdate,-1),'DD-MON-YYYY') "Previous Month"
2* from dual
3 /
SYSDATE Previous Mo
-------------------- -----------
02-FEB-2005 12:52:11 02-JAN-2005
Tamil
-
Hi tamilselvan,
1 select sysdate, to_char(add_months(sysdate,-1),'DD-MON-YYYY') "Previous Month"
2* from dual
3 /
SYSDATE Previous Mo
-------------------- -----------
02-FEB-2005 12:52:11 02-JAN-2005
--------------------------------------------------------------------------------
The problem is we do not want to use DD-MON-YYYY, however need to check by MON-YYYY.
Thanks
-
Originally posted by aph
The problem is we do not want to use DD-MON-YYYY, however need to check by MON-YYYY.
When you do this you are comparing two character strings together, and the result it dependent upon your language configuration.
So in English, "FEB-2006" will sort lower than "JAN-2001", therefore ("FEB-2006" < "JAN-2001") is true. So don't use TO_CHAR() when what you really want to do is TRUNC().
-
Ok!
let's say we are in the month of FEB-2005 regardless of DD, and I want to go one month back and see if previous month is JAN-2005.
In where clause for some reason we can not use or do not want to use DD-MON-YYYY from the sysdate. just want to check with the above format.
Thanks
-
PHP Code:
1 select sysdate, to_char(add_months(sysdate,-1),'MON-YYYY') "Previous Month"
2* from dual
15:02:03 SQL> /
SYSDATE Previous
--------- --------
02-FEB-05 JAN-2005
Tamil
-
Originally posted by aph
Ok!
let's say we are in the month of FEB-2005 regardless of DD, and I want to go one month back and see if previous month is JAN-2005.
In where clause for some reason we can not use or do not want to use DD-MON-YYYY from the sysdate. just want to check with the above format.
Thanks
Is this what you were looking for?
Code:
1 select trunc(sysdate, 'MM') "current month",
2* add_months(trunc(sysdate, 'MM'), -1) "Previous Month"
3* from dual
SQL > /
current m Previous
--------- ---------
01-FEB-05 01-JAN-05
Elapsed: 00:00:00.31
-
Originally posted by tamilselvan
PHP Code:
1 select sysdate, to_char(add_months(sysdate,-1),'MON-YYYY') "Previous Month"
2* from dual
15:02:03 SQL> /
SYSDATE Previous
--------- --------
02-FEB-05 JAN-2005
Tamil
I guess it really doesn't matter what day of the month it is when you do a add_months.
-
Us this to get the previous month.
Code:
select add_months(last_day(add_months(sysdate,-1))+1, -1) Previous_month from dual
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
|