Cannot construct data type date, some of the arguments have values which are not valid.

 drop table test 

go

create table test 

(ExpDate  varchar(20))

insert into test values 

('06/22'), ('01/22'), ('02/22'), ('02/20'), ('01/20')

;with cte as (

Select DATEFROMPARTS(2000 + CAST(right(ExpDate,2) AS INT), CAST(left(ExpDate,2) AS INT), 1) AS ExpDate

from test 

)

SELECT ExpDate

from cte

WHERE(ExpDate) BETWEEN DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0) AND DATEADD(MM,DATEDIFF(MM,0,GETDATE())+1,0)

/*

ExpDate

----------

2020-02-01

2020-01-01

*/

insert into test values ('13/22')

;with cte as (

Select DATEFROMPARTS(2000 + CAST(right(ExpDate,2) AS INT), CAST(left(ExpDate,2) AS INT), 1) AS ExpDate

from test 

)

SELECT ExpDate

from cte

WHERE(ExpDate) BETWEEN DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0) AND DATEADD(MM,DATEDIFF(MM,0,GETDATE())+1,0)

/*

ExpDate

----------

2020-02-01

2020-01-01

Msg 289, Level 16, State 1, Line 22

Cannot construct data type date, some of the arguments have values which are not valid.

*/

--using CTE tables  and DATEFROMPARTS: did not work 

--Cannot construct data type date, some of the arguments have values which are not valid.

-----USE TEMP TABLES INSTED OF CTE ,