I am provided a date in myy format from a source I cannot alter. I require this to be cast to a date. Ex:

I do have a method which works but I find it ugly and I'm certain there is a better way.

TRY_CAST(CONCAT(LEFT(NULLIF(field,''),LEN(field)-2),'/01/',RIGHT(NULLIF(field,''),2)) AS DATE)

Also have some issues with blank fields, hence the NULLIF() use.

Database compatibility is limited to 2008 (100).

CodePudding user response：

Try following way it gives better Result

select TRY_CAST(YorField as date) from YourTable

Sample example

select TRY_CAST('September 2005' as date)

Result: 2005-09-01

CodePudding user response：

One method would be the following. Firstly I use concatenation and RIGHT to ensure that the value is 4 characters in length. Then I STUFF and concatenation to create a date in the format dd/MM/yyyy. Next TRY_CONVERT is used to attempt to convert the date to a valid date; if it isn't valid then NULL will be returned. Lastly, in the SELECT the month's name and year are concatenated together.

SELECT D.ActualDate, --Actually a date
       CONCAT(DATENAME(MONTH,D.ActualDate),' ',DATEPART(YEAR, D.ActualDate)) --Meets OP's original requirement
FROM (VALUES('905'),('122'),('1216'))V(YourColumn)
     CROSS APPLY (VALUES(TRY_CONVERT(date,'01/'   STUFF(RIGHT(CONCAT('0000',V.YourColumn),4),3,0,'/20'),103)))D(ActualDate)

Though, honestly, the best thing you can do here is fix your design; don't store years as 2 digits, and a format like Myy is honestly terrible. There are several date and time data types, and you should really be using one of those for the date data.