I am writing a function in T-SQL returning the last day of the month regardless of the date input.
Here is my code:
Alter Function dbo.FN_Get_Last_Day_in_Month2
(@FN_InputDt Datetime)
Returns smalldatetime
as
Begin
Declare @Result smalldatetime
Set @Result =
case when @FN_InputDt <> 01-01-1900 then
DATEADD(m, DATEDIFF(M, 0,@FN_InputDt)+1, -1)
Else 0 End
Return @Result
End
The code is not working correctly, here is a test that shows the bad behavior:
SELECT dbo.fn_get_last_day_in_month (07-05-2010)
Here is the (incorrect) result:
2010-07-31 00:00:00
What is 07-05-2010...May 7th or July 5th? You need to use a safe date format, take a look at Setting a standard DateFormat for SQL Server
example from How to find the first and last days in years, months etc
DECLARE @d DATETIME
SET @d = '20100705' -- notice ISO format
SELECT
DATEADD(yy, DATEDIFF(yy, 0, @d), 0) AS FirstDayOfYear,
DATEADD(yy, DATEDIFF(yy, 0, @d)+1, -1) AS LastDayOfYear,
DATEADD(qq, DATEDIFF(qq, 0, @d), 0) AS FirstDayOfQuarter,
DATEADD(qq, DATEDIFF(qq, 0, @d)+1, -1) AS LastDayOfQuarter,
DATEADD(mm, DATEDIFF(mm, 0, @d), 0) AS FirstDayOfMonth,
DATEADD(mm, DATEDIFF(mm, 0, @d)+1, -1) AS LastDayOfMonth,
@d - DATEDIFF(dd, @@DATEFIRST - 1, @d) % 7 AS FirstDayOfWeek,
@d - DATEDIFF(dd, @@DATEFIRST - 1, @d) % 7 + 6 AS LastDayOfWeek
for just the day use day or datepart
select DAY(getdate()),
DATEPART(dd,GETDATE())