CODEWITHSUNDEEP
sqlsql-serverdate
David
David

Reputation: 25

SQL Server Last Day of the Month

I have a varchar field that contains dates in this format: 

YYYY-MM

I need to get the last day of the month and get the result in this format:

YYYYMMDD

For example:

DECLARE @date VARCHAR(7) = '2019-01'
SELECT REPLACE(@date, '-', '') + RIGHT(@date, 2) AS date

My output is: 20190101
The output I am looking for is: 20190131

Upvotes: 0

Views: 2234

Answers (3)

Arpit Srivastava
Arpit Srivastava

Reputation: 9

Try this 

Declare @date varchar(25)='2019-01'
Select format(EOMONTH(Cast(Concat(@date,'-01')  as date) ), 'yyyyMMdd')

Upvotes: 0

tkeen
tkeen

Reputation: 372

If eomonth is not available in your SQL Server version create a function;

    CREATE function [dbo].[LastDayofMonth](@Date datetime)
returns datetime
as
    begin
        return DATEADD(mi, -1, DATEADD(mm, DATEDIFF(m, 0, @Date) + 1, 0))       
    end

Format the result;

DECLARE @Date VARCHAR(7)
SET @Date = '2019-01'
SELECT CONVERT(VARCHAR(8),DBO.LastDayofMonth(CAST(@Date+'-01' as DATETIME)),112)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269793

Use EOMONTH() -- after converting the value:

select eomonth(convert(date, @date + '-01', 23))

If you want a string, use FORMAT() or CONVERT() to convert the value to a string.

Upvotes: 2

Related Questions