Why did YEAR fail with a conversion error from a date?

I got a presentation called "FechasFirmaHorometros", which is 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 called [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 hitting the above function when debugging another stored procedure that uses it

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

But I can freely do things like

SELECT DAY(FechaFirma) FROM FechasFirmaHorometros

Why is this happening and how can I solve or get around it?

+1
source share
1 answer

I assume that RValuesthis is a string column of some type, for some reason. You have to fix this and save the date data using the date data type (obviously in a separate column than this mixed package).

, , :

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

( "date" NULL, SQL Server , .)

, WHERE, SQL Server SELECT ( ). , , CTE, .. Connect - " " " ".

CASE, SQL Server ISDATE(), ( ), :

  • #temp #temp .
  • YEAR/MONTH ..
  • , YEAR = LEFT (col, 4) ..
  • TRY_CONVERT(), , SQL Server 2012:

    TRY_CONVERT(DATE, RValues) AS FechaFirma
    
+5

All Articles