How To Get the Last Day in a month

Question

Post reply

How To Get the Last Day in a month

Henmi

Ten Centuries

Points: 1015
More actions
July 14, 2003 at 2:52 am

#80527

Hi,

Given a month in a year, How we can get the last day in that month using T-SQL? Any ideas?

Thanks in advance,

Hendry

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi Hendry,

quote:

Given a month in a year, How we can get the last day in that month using T-SQL? Any ideas?

something like

SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,GETDATE())),DATEADD(m,1,GETDATE())))

?

Replace GETDATE() with the date in question, if it differs from the current month

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Here is another way.

select DAY(DATEADD(d,-1,DATEADD(m,DATEDIFF(m,0,GETDATE())+1,0)))

The bennifit is it is just a bit shorter and requires replacing only 1 GETDATE().

Or this which is even shorter

select DAY(DATEADD(m,DATEDIFF(m,0,GETDATE())+1,-1))

-1 represents 12/31/1899 and since the maximum number of days in a month is 31 if less then it will give you the highest value for the month for the date replacing GETDATE().

Edited by - antares686 on 07/14/2003 04:17:29 AM

muk07 Old Hand Points: 367 More actions · Answer 3

muk07

Old Hand

Points: 367

July 14, 2003 at 5:09 am

#465274

Take the 1st of next month and subtract 1

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Frank Kalis

SSC Guru

Points: 111183

July 14, 2003 at 5:24 am

#465276

Hi muk07,

quote:

Take the 1st of next month and subtract 1

how would this code look like?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

muk07 Old Hand Points: 367 More actions · Answer 5

try this function:

CREATE FUNCTION dbo.k_LastOfMonth(@Year int, @Month int)

RETURNS datetime AS

BEGIN

declare @Year1 int, @Month1 int, @d datetime

declare @S char(8), @Month2 char(2)

set @Year1=@Year

set @Month1=@Month+1

if @Month1>12 begin

set @Month1=1

set @Year1=@Year1+1

end

if @Month1<10

set @Month2='0'+cast(@Month1 as char(1))

else

set @Month2=cast(@Month1 as char(2))

set @S=cast(@Year1 as char(4))+@Month2+'01'

set @d=cast(@s as datetime)-1

return @d

END

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Frank Kalis

SSC Guru

Points: 111183

July 14, 2003 at 6:54 am

#465290

Hi muk07,

quote:

try this function:

...

well ok, when you place this in a function.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Antares686 SSC Guru Points: 125444 More actions · Answer 7

But consider what you have done with the function to get the results.

Now compare this

CREATE FUNCTION dbo.udf_LastDayOfMonth(@DateIn datetime)

RETURNS int

AS

BEGIN

return DAY(DATEADD(m,DATEDIFF(m,0,@DateIn)+1,-1))

END

Or If you want date then try

CREATE FUNCTION dbo.udf_LastDateOfMonth(@DateIn datetime)

RETURNS datetime

AS

BEGIN

return DATEADD(m,DATEDIFF(m,0,@DateIn)+1,-1)

END

SELECT dbo.udf_LastDayOfMonth('2/12/2000'), dbo.udf_LastDateOfMonth('2/12/2000')

Henmi Ten Centuries Points: 1015 More actions · Answer 8

Henmi

Ten Centuries

Points: 1015

July 16, 2003 at 10:41 pm

#465773

Thanks guys for the help. I learn alot here.

Cheers

Hendry