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 SSC-Addicted Points: 417 More actions · Answer 3

muk07

SSC-Addicted

Points: 417

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

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 SSC-Addicted Points: 417 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-2 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-2=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

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

Thanks guys for the help. I learn alot here.

Cheers

Hendry