Date conversion from "JAN02/19 to 2019-01-02

Question

How do I convert the values of a varchar(25) column from dates in the format "JAN02/19" to "2019-01-02"(YYYY-MM-DD)?

Answer 1

Perhaps something like this

Declare @S varchar(25)='JAN02/19'

Select try_convert(date,replace(@S,'/',' 20'))

Returns

2019-01-02

Answer 2

You can do something like below:

DECLARE @Date Varchar(10)
Set @Date='JAN02/19'
select DATEFROMPARTS('20'+substring(@Date,7,2),CHARINDEX(@Date,'JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC')/4+1,substring(@Date,4,2))

This should give the output in the desired format.

Edit:

To accomodate improper month values:

DECLARE @Date Varchar(10)
Set @Date='JAN02/19'
select DATEFROMPARTS('20'+substring(@Date,7,2),NULLIF(CHARINDEX(substring(@Date,1,3),'JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC'),0)/4+1,substring(@Date,4,2))

This will return NULL in case the month values are not proper.