Why would YEAR fail with a conversion error from a Date?

Question

I got a view named 'FechasFirmaHorometros' defined as

SELECT IdFormulario, 
       CONVERT(Date, RValues) AS FechaFirma 
FROM   dbo.Respuestas 
WHERE  ( IdPreguntas IN (SELECT IdPregunta 
                         FROM   dbo.Preguntas 
                         WHERE 
         ( FormIdentifier = dbo.IdFormularioHorometros() ) 
         AND ( Label = 'SLFYHDLR' )) ) 

And i have a Function named [RespuestaPreguntaHorometrosFecha] defined as

SELECT Respuestas.RValues 
FROM   Respuestas 
       JOIN Preguntas 
         ON Preguntas.Label = @LabelPregunta 
       JOIN FechasFirmaHorometros 
         ON FechasFirmaHorometros.IdFormulario = Respuestas.IdFormulario 
WHERE  Respuestas.IdPreguntas = Preguntas.IdPregunta 
       AND YEAR(FechasFirmaHorometros.FechaFirma) = @Anio 
       AND MONTH(FechasFirmaHorometros.FechaFirma) = @Mes

@LabelPregunta VARCHAR(MAX)
@Anio INT
@Mes INT

I keep getting this message upon hitting the aforementioned function while debugging another stored procedure that uses it

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

Yet i can freely do things like

SELECT DAY(FechaFirma) FROM FechasFirmaHorometros

Why is this happening and how can i solve or work around it?

Answer 1

I assume that RValues is a string column of some type, for some reason. You should fix that and store date data using a date data type (obviously in a separate column than this mixed bag).

If you can't fix that, then you can prevent what Damien described above by:

CASE WHEN ISDATE(RValues) = 1 THEN CONVERT(Date, RValues) END AS FechaFirma 

(Which will make the "date" NULL if SQL Server can't figure out how to convert it to a date.)

You can't prevent this simply by adding a WHERE clause, because SQL Server will often try to attempt the conversion in the SELECT list before performing the filter (all depends on the plan). You also can't force the order of operations by using a subquery, CTE, join order hints, etc. There is an open Connect item about this issue - they are "aware of it" and "hope to address it in a future version."

Short of a CASE expression, which forces SQL Server to evaluate the ISDATE() result before attempting to convert (as long as no aggregates are present in any of the branches), you could:

• dump the filtered results into a #temp table, and then subsequently select from that #temp table, and only apply the convert then.
• just return the string, and treat it as a date on the client, and pull YEAR/MONTH etc. parts out of it there
• just use string manipulation to pull YEAR = LEFT(col,4) etc.

• use TRY_CONVERT() since I just noticed you're on SQL Server 2012:

  TRY_CONVERT(DATE, RValues) AS FechaFirma