Home » SQL & PL/SQL » SQL & PL/SQL » Even & Odd Months (Oracle, 8.1.7, Windows Server 2003)
Even & Odd Months [message #361028] Mon, 24 November 2008 21:52 Go to next message
kumarvk
Messages: 211
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 #361029 is a reply to message #361028] Mon, 24 November 2008 22:08 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

What do you mean by Even and Odd months ?

Anyway , It might be possible .

Smile
Rajuvan.
Re: Even & Odd Months [message #361033 is a reply to message #361029] Mon, 24 November 2008 22:24 Go to previous messageGo to next message
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 Go to previous messageGo to next message
kumarvk
Messages: 211
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 #361036 is a reply to message #361028] Mon, 24 November 2008 22:52 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Ie, For OP Odd/Even is not based on the number of days in a month but based on Position of a month in an year.

That is why i asked the question

Quote:
What do you mean by Even and Odd months ?


Smile
Rajuvan.
Re: Even & Odd Months [message #361037 is a reply to message #361028] Mon, 24 November 2008 22:55 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Or simply you can add MOD in the where clause without hard coding.


Smile
Rajuvan.
Re: Even & Odd Months [message #361039 is a reply to message #361033] Mon, 24 November 2008 23:07 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
./fa/1587/0/

Anyways cheers ,
Rajat

[Updated on: Mon, 24 November 2008 23:23]

Report message to a moderator

Re: Even & Odd Months [message #361045 is a reply to message #361028] Mon, 24 November 2008 23:26 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Quote:
After (Noofdaysinmonth/2) we can tell odd or even


Here also MOD Function will do the trick . Anyway , OP is interested in position of month in an year.

Smile
Rajuvan.
Re: Even & Odd Months [message #361139 is a reply to message #361035] Tue, 25 November 2008 02:52 Go to previous messageGo to next message
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')...
Re: Even & Odd Months [message #361165 is a reply to message #361139] Tue, 25 November 2008 04:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
.. and while you're at it: use four-digit years.
Two-digit years is soooo pre-Y2K.
Re: Even & Odd Months [message #361235 is a reply to message #361165] Tue, 25 November 2008 08:15 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
D'oh!!!!


Embarassed
Previous Topic: Status: "301 Moved Permanently" internally altered by PL/SQL Agent to "302 Found"
Next Topic: giving user/pass in a DBURI call to the database
Goto Forum:
  


Current Time: Tue Dec 06 08:09:23 CST 2016

Total time taken to generate the page: 0.21974 seconds