Oracle

Get all the months that have 31 days

Please support our Oracle advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jul 2008

Posts: 1

Reputation: tanta is an unknown quantity at this point

Solved Threads: 0

tanta

Offline

Newbie Poster

Get all the months that have 31 days

Jul 23rd, 2008

Hi,
I am a junior dev. and I need help on some select statements on a Oracle db.
First I needed to get all the months of the year in 'MON' format and I did it like this:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT DISTINCT TO_CHAR(dt,'MON') MONTH
FROM ( SELECT TRUNC(SYSDATE,'y')-1+ROWNUM dt
FROM all_objects
WHERE ROWNUM <= 366)
ORDER BY TO_DATE( MONTH, 'MON' )
SELECT distinct TO_CHAR(dt,'MON') MONTH
FROM ( SELECT TRUNC(SYSDATE,'y')-1+ROWNUM dt
FROM all_objects
WHERE ROWNUM <= 366)
ORDER BY TO_DATE( MONTH, 'MON' )

Now, I need another statements to get only the month that have 31 days, then 30 days the leap year.

I cannot create strings that have the names like JANFEBMAR.. and then compare because we use this in a multilingual environment.

Any help would be appreciate it.

Last edited by peter_budo; Jul 23rd, 2008 at 8:11 pm. Reason: Keep It Organized - please use [code] tags

•

Join Date: Apr 2008

Posts: 296

Reputation: tesuji is on a distinguished road

Solved Threads: 42

tesuji

Offline

Posting Whiz in Training

Re: Get all the months that have 31 days

Jul 25th, 2008

Hi,

you may try:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT * FROM yourtable WHERE TO_CHAR(your_date, 'MM') IN ('01', '03',... ,'12')
select * from yourtable where TO_CHAR(your_date, 'MM') in ('01', '03',... ,'12')

krs,
tesu

Last edited by peter_budo; Jul 26th, 2008 at 5:11 pm. Reason: Keep It Organized - please use [code] tags

Information is moving—you know, nightly news is one way, of course, but it's also moving through the blogosphere and through the Internets. I promise you I will listen to what has been said here, even though I wasn't here. Ann and I will carry out this equivocal message to the world. I'm the master of low expectations.