Function get the last day of month in sql

Question

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 1

You would do something like this:

select eomonth(getdate(), 0);

If you want it formatted as MM/YYYY then you'd do this:

select format(eomonth(getdate(), 0), 'MM/yyyy');

Answer 2

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:

• Some Common Date Routines

Answer 3

Pardon me for tossing-in a response that is not specific to "SQL Server," nor thence to "2005," but the generalized way to compute the answer that you seek is as follows:

1. Break down the input that you have, e.g. 06/2016, into two parts. Call 'em @MONTH and @YEAR. Define a third value, @DAY, equal to 1.
2. Typecast this into a date-value ... "June 1, 2016."
3. Now, using the date-handling functions that you're sure to have, "first add one month, then subtract one day."

One thing that you must be very careful of, when designing code like this, is to be certain(!) that your code for decoding 06/2016 works for every(!) value that actually occurs in that database, or that it can be relied upon to fail.

Answer 4

try this,

declare @input varchar(20)='06/2016'
set @input=@input+'/01'
declare @dtinput datetime=@input


select  dateadd(day,-1,dateadd(month,datediff(month,0,@dtinput)+1,0))
--OR in sql server 2012
select eomonth(@dtinput)