Conversion failed when converting date and/or time from character string (Return the last day of month)

you didn't declare the size for the varchar for @date. So basically @date only contains the character 2

DECLARE @date VARCHAR(6) = '201809';  


-- Month and Year

DECLARE @mes VARCHAR(2), @ano VARCHAR(4)
SET @mes = SUBSTRING(@date, 5, 6)   
SET @ano = SUBSTRING(@date, 1, 4)

--Return last day of Month

SELECT DAY(DATEADD(d,-1,DATEADD(M,1,CONVERT(DATETIME,@ano + @mes + '01')))) 

if you are using SQL Server 2012 or later, you can simply

DECLARE @date VARCHAR(6) = '201809';  
SELECT DAY(EOMONTH(CONVERT(DATETIME, @date + '01', 112)))

Assuming Version 2012 or higher, you can use the built in EOMonth function to get the last date of the month.

Also, please note (as also mentioned in Squirrel's answer) that you didn't specify the length of the varchar when declaring the @Date variable - This means that SQL Server will create it as varchar(1) and truncate the value to 2.

DECLARE @date VARCHAR(6) = '201809';

SELECT EOMONTH(@date + '01')

Please note that yyyymmdd format is the alternative ISO8601 format, meaning that SQL Server can implicitly convert a string representation of date values in this format to Date and will always do it correctly regardless of local settings or even explicitly setting dateformat to any other value (dmy, mdy etc')

Its working please try this

DECLARE @date CHAR(10) = '201809';  


-- Month and Year

DECLARE @mes VARCHAR(2), @ano VARCHAR(4)    SET @mes = SUBSTRING(@date, 5, 6)   SET @ano = SUBSTRING(@date, 1, 4)

--Return last day of Month

SELECT DAY(DATEADD(d,-1,DATEADD(M,1,CONVERT(DATETIME,@ano + @mes + '01')))) 

you should declare varchar(6)

DECLARE @date VARCHAR(6) = '201809';  

-- Month and Year
select @date
DECLARE @mes VARCHAR(2), @ano VARCHAR(4)
SET @mes = SUBSTRING(@date, 5, 6)   
SET @ano = SUBSTRING(@date, 1, 4)
select @mes,@ano

--Return last day of Month
SELECT DAY(DATEADD(d,-1,DATEADD(M,1,CONVERT(DATETIME,@ano + @mes + '01'))))

1: you should declare varchar(6)

DECLARE @date VARCHAR(6) = '201809';  

-- Month and Year
select @date
DECLARE @mes VARCHAR(2), @ano VARCHAR(4)
SET @mes = SUBSTRING(@date, 5, 6)   
SET @ano = SUBSTRING(@date, 1, 4)
select @mes,@ano

--Return last day of Month
SELECT DAY(DATEADD(d,-1,DATEADD(M,1,CONVERT(DATETIME,@ano + @mes + '01'))))

[Screen shot][0]

From SQL 2012 you can use EOMonth

DECLARE @currentDate DATE = CURRENT_TIMESTAMP
SELECT EOMONTH (@currentDate) AS LastDayOfMonth