Home » SQL & PL/SQL » SQL & PL/SQL » Number of days in current month
Number of days in current month [message #261582] Thu, 23 August 2007 01:33 Go to next message
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
Re: Number of days in current month [message #261596 is a reply to message #261582] Thu, 23 August 2007 01:58 Go to previous messageGo to next message
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
Re: Number of days in current month [message #261600 is a reply to message #261582] Thu, 23 August 2007 02:02 Go to previous messageGo to next message
Maaher
Messages: 7062
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
Re: Number of days in current month [message #261602 is a reply to message #261596] Thu, 23 August 2007 02:04 Go to previous messageGo to next message
Maaher
Messages: 7062
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
Re: Number of days in current month [message #261604 is a reply to message #261602] Thu, 23 August 2007 02:05 Go to previous messageGo to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
Thanks Maheer.

Re: Number of days in current month [message #261639 is a reply to message #261582] Thu, 23 August 2007 03:24 Go to previous messageGo to next message
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
Re: Number of days in current month [message #261648 is a reply to message #261639] Thu, 23 August 2007 03:56 Go to previous messageGo to next message
Maaher
Messages: 7062
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
icon14.gif  Re: Number of days in current month [message #262019 is a reply to message #261582] Fri, 24 August 2007 06:21 Go to previous message
satya.das2007
Messages: 33
Registered: February 2007
Location: India
Member
Thanks all of you!!!

ofcourse, feb 2008 has 29 days...oops missed out... Razz
Previous Topic: Invalid character error
Next Topic: Quick Question
Goto Forum:
  


Current Time: Sat Dec 10 20:27:36 CST 2016

Total time taken to generate the page: 0.08406 seconds