OraFAQ Forum: SQL & PL/SQL » Number of days in current month

Home » SQL & PL/SQL » SQL & PL/SQL » Number of days in current month

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Number of days in current month [message #261582]

Thu, 23 August 2007 01:33

satya.das2007
Messages: 33
Registered: February 2007
Location: India

Member

Hi,

I am looking to generate a query that will fetch number of days in current month.

Eg:

if i run the query today (23-Aug-2007) then the result will be 31.

Similarly, if i run the same query on 02-Sep-2007 and 28-feb-2008 then the result will be 30 and 28 respectively.

Can anyone help me out?

Thanks in advance,
Satya

Report message to a moderator

Re: Number of days in current month [message #261596 is a reply to message #261582]

Thu, 23 August 2007 01:58

darshanmeel
Messages: 44
Registered: June 2007
Location: India

Member

Hi Satya

There might be some user provided function for this.But you could do it like this as well.Trunc could be dropped from the query.

select 
substr(to_char
(last_day
(trunc(sysdate)
)
),1,2) 
from dual;

This might help you.

Darshan Singh

Report message to a moderator

Re: Number of days in current month [message #261600 is a reply to message #261582]

Thu, 23 August 2007 02:02

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Satya, in Belgium - where I come from - we have 29 days in February 2008 Wink

from the SQL Reference

LAST_DAY returns the date of the last day of the month that contains date.

Look at this:

SQL> SELECT TO_DATE('23-Aug-2007','dd-mon-yyyy') thedate
  2       , LAST_DAY(TO_DATE('23-Aug-2007','dd-mon-yyyy')) lastday
  3  FROM   dual
  4  /

THEDATE   LASTDAY
--------- ---------
23-AUG-07 31-AUG-07

SQL>
SQL> SELECT TO_DATE('02-Sep-2007','dd-mon-yyyy') thedate
  2       , LAST_DAY(TO_DATE('02-Sep-2007','dd-mon-yyyy')) lastday
  3  FROM   dual
  4  /

THEDATE   LASTDAY
--------- ---------
02-SEP-07 30-SEP-07

SQL>
SQL> SELECT TO_DATE('28-feb-2008','dd-mon-yyyy')  thedate
  2       , LAST_DAY(TO_DATE('28-feb-2008','dd-mon-yyyy')) lastday
  3  FROM   dual
  4  /

THEDATE   LASTDAY
--------- ---------
28-FEB-08 29-FEB-08

All you need to do is convert the second date column into a single number. A bit of TO_CHAR and TO_NUMBER will take care of that Wink

.

MHE

Report message to a moderator

Re: Number of days in current month [message #261602 is a reply to message #261596]

Thu, 23 August 2007 02:04

Maaher
Messages: 7065
Registered: December 2001

Senior Member

darshanmeel wrote on Thu, 23 August 2007 08:58

Hi Satya

There might be some user provided function for this.But you could do it like this as well.Trunc could be dropped from the query.

select 
substr(to_char
(last_day
(trunc(sysdate)
)
),1,2) 
from dual;

This might help you.

Darshan Singh

You got the same Idea, Darshan, but why not add a date format to your TO_CHAR? That way you can get rid of the SUBSTR.

MHE

Report message to a moderator

Re: Number of days in current month [message #261604 is a reply to message #261602]

Thu, 23 August 2007 02:05

darshanmeel
Messages: 44
Registered: June 2007
Location: India

Member

Thanks Maheer.

Report message to a moderator

Re: Number of days in current month [message #261639 is a reply to message #261582]

Thu, 23 August 2007 03:24

ShivrajGutte
Messages: 21
Registered: April 2007
Location: PUNE

Junior Member

hi,
select to_char(sysdate,'MON'),to_char(last_day(sysdate),'dd') from dual.

Regards
Shiv

Report message to a moderator

Re: Number of days in current month [message #261648 is a reply to message #261639]

Thu, 23 August 2007 03:56

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Maaher wrote on Thu, 23 August 2007 09:04

...but why not add a date format to your TO_CHAR? That way you can get rid of the SUBSTR.

ShivrajGutte wrote on Thu, 23 August 2007 10:24

hi,
select to_char(sysdate,'MON'),to_char(last_day(sysdate),'dd') from dual.

That was the idea, yes.

MHE

Report message to a moderator

Re: Number of days in current month [message #262019 is a reply to message #261582]

Fri, 24 August 2007 06:21

satya.das2007
Messages: 33
Registered: February 2007
Location: India

Member

Thanks all of you!!!

ofcourse, feb 2008 has 29 days...oops missed out... Razz

Report message to a moderator

Previous Topic:	Invalid character error
Next Topic:	Quick Question

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 20:09:36 CST 2024