Even & Odd Months [message #361028] |
Mon, 24 November 2008 21:52  |
kumarvk
Messages: 214 Registered: July 2004
|
Senior Member |
|
|
Hi All,
I just want to know is there any command available to extract even / odd months separately.
For Example:
SELECT job_no,out_date from stk_out
where stk_out_date BETWEEN '01-JAN-08' AND '31-OCT-08';
from this I want to extract even / odd months separately.
Can anyone help in this regard?
|
|
|
|
Re: Even & Odd Months [message #361033 is a reply to message #361029] |
Mon, 24 November 2008 22:24   |
rajatratewal
Messages: 507 Registered: March 2008 Location: INDIA
|
Senior Member |
|
|
Why don't you first try to find number of days in a Month.
From that you can find whether the given month is Odd or Even.
Hint:-
Search LAST_DAY
Regards,
Rajat Ratewal
|
|
|
Re: Even & Odd Months [message #361035 is a reply to message #361028] |
Mon, 24 November 2008 22:48   |
kumarvk
Messages: 214 Registered: July 2004
|
Senior Member |
|
|
Hi,
I found it myself, I did like this
SELECT job_no,out_date from stk_out
where stk_out_date BETWEEN '01-JAN-08' AND '31-OCT-08'
AND TO_CHAR(out_date,'MM')
-- even months
IN ('02', '04', '06', '08', '10', '12');
-- odd months
-- IN ('01', '03', '05', '07', '09', '11');
|
|
|
|
|
Re: Even & Odd Months [message #361039 is a reply to message #361033] |
Mon, 24 November 2008 23:07   |
joicejohn
Messages: 327 Registered: March 2008 Location: India
|
Senior Member |
|
|
rajatratewal wrote on Tue, 25 November 2008 09:54 | Why don't you first try to find number of days in a Month.
From that you can find whether the given month is Odd or Even.
Hint:-
Search LAST_DAY
Regards,
Rajat Ratewal
|
I don't think this logic will work. Check out your logic for August and the months after that.
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Nov 25 10:29:36 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Enter user-name: test_schema
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT LAST_DAY(TO_DATE('01-JUL-2008', 'DD-MON-YYYY')) LastDay,
2 TO_CHAR(TO_DATE('01-JUL-2008', 'DD-MON-YYYY'), 'MM') FROM Dual;
LASTDAY TO
--------- --
31-JUL-08 07
SQL>
SQL> SELECT LAST_DAY(TO_DATE('01-AUG-2008', 'DD-MON-YYYY')) LastDay,
2 TO_CHAR(TO_DATE('01-AUG-2008', 'DD-MON-YYYY'), 'MM') FROM Dual;
LASTDAY TO
--------- --
31-AUG-08 08
SQL>
SQL> SELECT LAST_DAY(TO_DATE('01-SEP-2008', 'DD-MON-YYYY')) LastDay,
2 TO_CHAR(TO_DATE('01-SEP-2008', 'DD-MON-YYYY'), 'MM') FROM Dual;
LASTDAY TO
--------- --
30-SEP-08 09
SQL>
SQL> SELECT LAST_DAY(TO_DATE('01-OCT-2008', 'DD-MON-YYYY')) LastDay,
2 TO_CHAR(TO_DATE('01-OCT-2008', 'DD-MON-YYYY'), 'MM') FROM Dual;
LASTDAY TO
--------- --
31-OCT-08 10
SQL>
[***Added: Didn't see Rajuvan's reply. Didn't quite understand the link between your query and OP's requirement. My Apologies...]
Regards,
Jo
[Updated on: Mon, 24 November 2008 23:19] Report message to a moderator
|
|
|
Re: Even & Odd Months [message #361043 is a reply to message #361039] |
Mon, 24 November 2008 23:20   |
rajatratewal
Messages: 507 Registered: March 2008 Location: INDIA
|
Senior Member |
|
|
joicejohn i thought that the OP needs to find out the month
odd or even based on number of days in that month.
I have given a hint in which he can find last_day after using that function and use add_month function to find number of days
in that month.After (Noofdaysinmonth/2) we can tell odd or even.
Oops i did'nt see either that you have modified your post.

Anyways cheers ,
Rajat
[Updated on: Mon, 24 November 2008 23:23] Report message to a moderator
|
|
|
|
Re: Even & Odd Months [message #361139 is a reply to message #361035] |
Tue, 25 November 2008 02:52   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You should really fix the glaring bugs in thsi code:SELECT job_no,out_date from stk_out
where stk_out_date BETWEEN '01-JAN-08' AND '31-OCT-08'
AND TO_CHAR(out_date,'MM')...
'01-JAN-08' is not a date, and neither is '31-OCT-08'. They're both strings.
Something like this will have a much smaller chance of unexpectedly failing:SELECT job_no,out_date from stk_out
where stk_out_date BETWEEN to_date('01-JAN-08','dd-mon-yyyy') AND to_date('31-OCT-08','dd-mon-yyyy')
AND TO_CHAR(out_date,'MM')...
|
|
|
|
|