Conversion failed error when joining on a table with a created field

I have a table (DateDimension) with a field (MonthYear) that is a varchar. The values look like this:

12 2017 11 2017 10 2017 9 2017

I have another table (SupplierData) with a field (GR Date) that is a varchar that I am using to create a field (ReportingPeriod) to use as a join to the DateDimension table.

Here is the code for ReportingPeriod:

ReportingPeriod = Cast(DatePart(Month, [GR Date]) As varchar(2)) + ' ' + Cast(DatePart(Year, [GR Date]) As Varchar(4))

When I try to create my join like this:

INNER JOIN dbo.DateDimension dd ON dd.MonthYear = a.ReportingPeriod

I receive this error:

Conversion failed when converting date and/or time from character string.

How can I resolve this?

Solution

Sounds like your error is caused by a bad value for GR Date. The best solution is to make sure you're using correct datatypes, but you could also use try_cast() instead as it will return null instead of throwing an error when the value can't be converted.

ReportingPeriod = Cast(DatePart(Month, try_cast([GR Date] as date)) As varchar(2)) 
  + ' ' 
  + Cast(DatePart(Year, try_cast([GR Date] as date)) As Varchar(4))

In Sql Server 2012 and up: each of these will return null when the conversion fails instead of an error.