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
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
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
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
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With