Home » SQL & PL/SQL » SQL & PL/SQL » Split Months (Oracle 10G)
Split Months [message #569531] Mon, 29 October 2012 05:15 Go to next message
ORA_KRISH
Messages: 38
Registered: March 2011
Location: chennai
Member
Hi All,


I need to display the month as below, in a sql statements.

MONTH

JAN-1-15
JAN-16-31
FEB-1-15
FEB-15-28
MAR-1-15
MAR-15-31

i tired with this, but it dint come


select trunc(sysdate, 'year') one, trunc(sysdate, 'year') + floor((last_day(trunc(sysdate, 'year')) + 1 - trunc(sysdate, 'year'))/2) - 1 two,
trunc(sysdate, 'year') + floor((last_day(trunc(sysdate, 'year')) + 1 - trunc(sysdate, 'year'))/2) three, last_day(trunc(sysdate, 'year')) four from dual





Regards

Hari g
Re: Split Months [message #569545 is a reply to message #569531] Mon, 29 October 2012 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 59791
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Tricky work on dates:
SQL> select to_char(add_months(trunc(sysdate,'YEAR'),trunc((level-1)/2))
  2                 + 15*mod(level-1,2)
  3                , 'MON-DD')
  4         ||'-'||
  5         to_char(least(add_months(trunc(sysdate,'YEAR'),trunc((level-1)/2)) 
  6                       + 15*(1+mod(level-1,2)) - mod(level,2)
  7                      ,add_months(trunc(sysdate,'YEAR'),trunc((level-1)/2)+1)-1) 
  8                , 'DD') res
  9  from dual
 10  connect by level <= 24
 11  order by level
 12  /
RES
---------
JAN-01-15
JAN-16-31
FEB-01-15
FEB-16-29
MAR-01-15
MAR-16-31
APR-01-15
APR-16-30
MAY-01-15
MAY-16-31
JUN-01-15
JUN-16-30
JUL-01-15
JUL-16-31
AUG-01-15
AUG-16-31
SEP-01-15
SEP-16-30
OCT-01-15
OCT-16-31
NOV-01-15
NOV-16-30
DEC-01-15
DEC-16-31

Regards
Michel
Re: Split Months [message #569547 is a reply to message #569545] Mon, 29 October 2012 06:31 Go to previous message
ORA_KRISH
Messages: 38
Registered: March 2011
Location: chennai
Member
Hi Michel,

Simply superb, thanks a lot


Regards

Hari g
Previous Topic: basic DBA project
Next Topic: XML encoding to UTF-8 charset - Oracle 9i
Goto Forum:
  


Current Time: Wed Nov 26 09:32:16 CST 2014

Total time taken to generate the page: 0.13690 seconds