Hanaka Hanaka - 5 months ago 28
SQL Question

Function get the last day of month in sql

I need to get the last day of month with input of month and year. For example, with input 06/2016 it will return 30. I use Sql server 2005. Thanks for any help.

Answer

Suppose your input is VARCHAR in the form of MM/YYYY.

Use RIGHT and LEFT to get the year and month respectively. Then use DATEFROMPARTS to generate the starting date. Next, use EOMONTH to get the last day of the month. Finally use DAY to extract the day part.

DECLARE @input VARCHAR(7) = '06/2016'

SELECT
    DAY(
        EOMONTH(
            DATEFROMPARTS(CAST(RIGHT(@input,4) AS INT),CAST(LEFT(@input, 2) AS INT),1)
        )
    )

The above only works for SQL Server 2012+.


For SQL Server 2005, you can use DATEADD to generate the dates:

SELECT
    DAY( -- Day part
        DATEADD(DAY, -1, -- Last day of the month
            DATEADD(MONTH, CAST(LEFT(@input, 2) AS INT), -- Start of next month
                DATEADD(YEAR, CAST(RIGHT(@input, 4) AS INT) - 1900, 0) -- Start of the year
            )
        )
    )

Reference: