Not a valid month [message #430612] |
Wed, 11 November 2009 03:34  |
|
SELECT CASE
WHEN SYSDATE
BETWEEN '01-jan-'
|| TO_CHAR (ADD_MONTHS (:pdate, -12), 'RRRR')
AND LAST_DAY (ADD_MONTHS (:pdate, -12))
THEN 'a'
ELSE 'b'
END
FROM DUAL
|
|
|
|
Re: Not a valid month [message #430617 is a reply to message #430612] |
Wed, 11 November 2009 03:46   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Try this:
SELECT CASE WHEN SYSDATE BETWEEN to_date('01-jan-'|| TO_CHAR (ADD_MONTHS (:pdate, -12), 'RRRR'),'dd-mon-yyyy')
AND LAST_DAY (ADD_MONTHS (:pdate, -12))
THEN 'a'
ELSE 'b'
END
FROM DUAL
|
|
|
|
Re: Not a valid month [message #430625 is a reply to message #430612] |
Wed, 11 November 2009 04:27   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
And, as January, 1st is the first day in the year, then SELECT CASE WHEN SYSDATE BETWEEN TRUNC (ADD_MONTHS (:pdate, -12), 'yyyy')
AND LAST_DAY (ADD_MONTHS (:pdate, -12))
THEN 'a'
ELSE 'b'
END
FROM DUAL will return the same result without any date/string conversions.
|
|
|
|
Re: Not a valid month [message #430653 is a reply to message #430612] |
Wed, 11 November 2009 08:08   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
seyed456 wrote on Wed, 11 November 2009 04:34
BETWEEN '01-jan-'
|| TO_CHAR (ADD_MONTHS (:pdate, -12), 'RRRR')
AND LAST_DAY (ADD_MONTHS (:pdate, -12))
You really need to understand about implicit conversion as Thomas has suggested. Please read the concepts manual of Oracle.
Your query in essence is the same as "Is today's date between an earthworm and 1 year ago?"
|
|
|
|
|
|
Re: Not a valid month [message #430767 is a reply to message #430759] |
Thu, 12 November 2009 05:12   |
ayush_anand
Messages: 417 Registered: November 2008
|
Senior Member |
|
|
Michel wroteDoes your new avatar means "don't care of my posts they are just a silly jokes"?
Regards
Michel
i dont think a will come for any "inputted" date ,So
Meanwhile my avatar means "Be happy"
sorry take it as joke and ignore it
as it gives a for this
SQL> SELECT CASE WHEN SYSDATE BETWEEN to_date('01-jan-'|| TO_CHAR (ADD_MONTHS (SYSDATE+365, -12),
'RRRR'),'dd-mon-yyyy')
2 AND LAST_DAY (ADD_MONTHS (SYSDATE+365, -12))
3 THEN 'a'
4 ELSE 'b'
5 END
6 FROM DUAL
7 /
C
-
a
[Updated on: Thu, 12 November 2009 05:20] Report message to a moderator
|
|
|
|
|
|
|