Last Day of the Month

23 Dec 2005 7:41 PM

Vishal

Hi,

is there a way to find the last day of the month ?
the input parameter is just the month. year will be the current year, start
date will always be 1st of the month. I need to find the last day of the
month

Thanks

23 Dec 2005 8:03 PM

Andrew J. Kelly

http://www.aspfaq.com/show.asp?id=2444

--
Andrew J. Kelly SQL MVP

Show quote

"Vishal" <vrajpu***@hotmail.com> wrote in message
news:ems8Wj$BGHA.3812@TK2MSFTNGP15.phx.gbl...
> Hi,
>
> is there a way to find the last day of the month ?
> the input parameter is just the month. year will be the current year,
> start date will always be 1st of the month. I need to find the last day of
> the month
>
> Thanks
>

23 Dec 2005 8:52 PM

Mark Williams

Maybe not as elegate as other solutions, but

SELECT DATEADD(dd, -1, CAST(YEAR(DATEADD(mm, 1, GETDATE())) AS char(4)) +
RIGHT('0' + CAST(MONTH(DATEADD(mm, 1, GETDATE())) AS varchar(2)), 2) + '01')

will get you the last day of the current month, in datetime format. If you
want only the numeric portion for the day, then

SELECT DAY(DATEADD(dd, -1, CAST(YEAR(DATEADD(mm, 1, GETDATE())) AS char(4))
+ RIGHT('0' + CAST(MONTH(DATEADD(mm, 1, GETDATE())) AS varchar(2)), 2) +
'01'))

DISCLAIMER: This will not work 7,995 years from now. ;-)

Show quote

"Vishal" wrote:

> Hi,
>
> is there a way to find the last day of the month ?
> the input parameter is just the month. year will be the current year, start
> date will always be 1st of the month. I need to find the last day of the
> month
>
> Thanks
>
>
>

23 Dec 2005 9:00 PM

Mark Williams

Speaking of more elegant solutions:

SELECT DATEADD(dd, -1, LEFT(CONVERT(char(8), DATEADD(mm, 1, GETDATE()),
112),6) + '01')

for the last day of the current month in datetime format

SELECT DAY(DATEADD(dd, -1, LEFT(CONVERT(char(8), DATEADD(mm, 1, GETDATE()),
112),6) + '01'))

for just the numeric date portion.

Show quote

"Mark Williams" wrote:

> Maybe not as elegate as other solutions, but
>
> SELECT DATEADD(dd, -1, CAST(YEAR(DATEADD(mm, 1, GETDATE())) AS char(4)) +
> RIGHT('0' + CAST(MONTH(DATEADD(mm, 1, GETDATE())) AS varchar(2)), 2) + '01')
>
> will get you the last day of the current month, in datetime format. If you
> want only the numeric portion for the day, then
>
> SELECT DAY(DATEADD(dd, -1, CAST(YEAR(DATEADD(mm, 1, GETDATE())) AS char(4))
> + RIGHT('0' + CAST(MONTH(DATEADD(mm, 1, GETDATE())) AS varchar(2)), 2) +
> '01'))
>
> DISCLAIMER: This will not work 7,995 years from now. ;-)
>
> "Vishal" wrote:
>
> > Hi,
> >
> > is there a way to find the last day of the month ?
> > the input parameter is just the month. year will be the current year, start
> > date will always be 1st of the month. I need to find the last day of the
> > month
> >
> > Thanks
> >
> >
> >

23 Dec 2005 9:01 PM

Mark Williams

Speaking of more elegant solutions:

SELECT DATEADD(dd, -1, LEFT(CONVERT(char(8), DATEADD(mm, 1, GETDATE()),
112),6) + '01')

for the last day of the current month in datetime format

SELECT DAY(DATEADD(dd, -1, LEFT(CONVERT(char(8), DATEADD(mm, 1, GETDATE()),
112),6) + '01'))

for just the numeric day of the month.

Show quote

"Mark Williams" wrote:

> Maybe not as elegate as other solutions, but
>
> SELECT DATEADD(dd, -1, CAST(YEAR(DATEADD(mm, 1, GETDATE())) AS char(4)) +
> RIGHT('0' + CAST(MONTH(DATEADD(mm, 1, GETDATE())) AS varchar(2)), 2) + '01')
>
> will get you the last day of the current month, in datetime format. If you
> want only the numeric portion for the day, then
>
> SELECT DAY(DATEADD(dd, -1, CAST(YEAR(DATEADD(mm, 1, GETDATE())) AS char(4))
> + RIGHT('0' + CAST(MONTH(DATEADD(mm, 1, GETDATE())) AS varchar(2)), 2) +
> '01'))
>
> DISCLAIMER: This will not work 7,995 years from now. ;-)
>
> "Vishal" wrote:
>
> > Hi,
> >
> > is there a way to find the last day of the month ?
> > the input parameter is just the month. year will be the current year, start
> > date will always be 1st of the month. I need to find the last day of the
> > month
> >
> > Thanks
> >
> >
> >

23 Dec 2005 9:30 PM

Mark Williams

OK, I promise this is my last post in this thread. I finally got my head on
straight

SELECT DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE())+1, 0))

SELECT DAY(DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE())+1, 0)))

Show quote

"Vishal" wrote:

> Hi,
>
> is there a way to find the last day of the month ?
> the input parameter is just the month. year will be the current year, start
> date will always be 1st of the month. I need to find the last day of the
> month
>
> Thanks
>
>
>

24 Dec 2005 12:18 AM

Vishal

Hi,

This is what I want to do:

User enters parameters StoreNo. Month(11), Year(2005), Open Months(8).

Store Table :
Store No. INT (IDENTITY)
OpenDate (DATETIME)

When the user calls a S Proc with the above parameters, I want to check
whether the Store was opened for 8 Months from the OpenDate on the store, if
the OpenDate is 22/02/2004 then the number of months should be calculated
from the next month, not including the month the store opened.

I would like to write this in a Function, please help.

Thanks

Show quote

"Vishal" <vrajpu***@hotmail.com> wrote in message
news:ems8Wj$BGHA.3812@TK2MSFTNGP15.phx.gbl...
> Hi,
>
> is there a way to find the last day of the month ?
> the input parameter is just the month. year will be the current year,
> start date will always be 1st of the month. I need to find the last day of
> the month
>
> Thanks
>