Tutorial :TSQL need to return last day of month only, how do I drop year, month & time?


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())  


Cast the return value to a SQL datetime type, and then call the "DAY" function to get the day in as an integer. See the function reference here:


Not sure which database you're using, but this should be a standard function across all databases.


I'd return a DATETIME, I've had trouble with SMALLDATETIME in the past.

DECLARE @Result DATETIME    SET @Result = DATEADD(m , 1, @FN_Input);    RETURN CAST(FLOOR(CAST(DATEADD(d, DATEPART(d, @Result) * -1, @Result) AS FLOAT)) AS DATETIME)  

Also, I think you may be a victim of SQL's complete disregard of date formatting. Always, always, always, when typing a string into test a SQL function use the following format;

'05 Jul 2010'

Your function probably works but it interpreted your date as 5th July - not 7th May.


DECLARE @date DATETIME = '20130624';  SELECT Day(EOMONTH ( @date )) AS LastDay;  GO  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »