CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Phattharawit Sombatrat
Phattharawit Sombatrat

Reputation: 115

How to get last date of month SQL Server 2008

I created a function "ufngetFirstDateOfMonth" and "ufngetLastDateOfMonth" stored in Microsoft SQL Server 2008. My purpose is to send some date into the function and it will return the first date of month with '00:00:00' or the last date of month with '23:59:59'.

I call the function like this:

exec ufngetLastDateOfMonth('2014-10-15')

and normally it returns '2014-10-31 23:59:59'
but when I send the last date of months that have 31 days (august, january,...):

exec ufngetLastDateOfMonth('2014-10-31')

it return '2014-10-30 23:59:59' whick is not correct Actally, it should be '2014-10-31 23:59:59'

Something goes wrong here...

This is my function:

CREATE FUNCTION [dbo].[ufnLastDateOfMonth](@Date date)
RETURNS varchar(50)

AS
BEGIN

DECLARE @New_Date varchar(50)

select @New_date = cast(dateadd(dd,-(DAY(@Date )),DATEADD(mm,1,@Date ))as varchar(50)) + ' 23:59:59'

RETURN @New_Date

END

Upvotes: 11

Views: 51383

Answers (6)

Md Shahriar
Md Shahriar

Reputation: 2786

Just try this:

SELECT EOMONTH('2000/2/1') Last_Date

Note: EOMONTH function is available in SQL Server version >= 2012

Upvotes: -1

Khwahish Batra
Khwahish Batra

Reputation: 1

you can try this

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

or this

select DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) As StartDateOfCurrentMonth

Upvotes: 0

Syakur Rahman
Syakur Rahman

Reputation: 2102

For those who are using SQL Server 2012, EOMONTH function could be an alternative.

DECLARE @date DATETIME = '12/1/2011';
SELECT EOMONTH ( @date ) AS Result;
GO

Source: https://msdn.microsoft.com/en-us/library/hh213020.aspx

Upvotes: 6

Manish Sharma
Manish Sharma

Reputation: 2426

Use this

DECLARE @curdate datetime;
SET @curdate = GETDATE(); -- you can pass your date here
--SET @curdate = '2014-10-15'; Example
SELECT DATEADD(MONTH,1+DATEDIFF(MONTH,0,@curdate),-1);

OR

SELECT DATEADD(MONTH,1+DATEDIFF(MONTH,0,GETDATE()),-1);

Upvotes: 1

Tanner
Tanner

Reputation: 22753

To get the last day you can do this:

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,'2014-08-12')+1,0))

Adding to your function:

select @New_date = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@date)+1,0))

Source:

SQL SERVER – Find Last Day of Any Month – Current Previous Next

Upvotes: 15

Gordon Linoff
Gordon Linoff

Reputation: 1270653

Go to the first day of the month. Add one month. Then subtract one day. Or, in your case, one second:

select @New_date = dateadd(second, -1, dateadd(month, 1, dateadd(day, -(DAY(@Date) + 1)) ) )

You should use convert() if you want this as a string. I would instead suggest that the function return a datetime.

Upvotes: 2

Related Questions