Error converting nvarchar data type to date - SQL Server

Question

Error converting nvarchar data type to date - SQL Server

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I am generating a cross table where I show the operators and every day of the week with the number of tickets they attended per day, in this case I am creating a function that receives a date parameter and calculates each day of the week for so generate my table.

CREATE PROCEDURE sp_reporteSemana @FECHA DATE
AS
DECLARE @INICIO_SEMANA AS DATE
DECLARE @MARTES AS DATE
DECLARE @MIERCOLES AS DATE
DECLARE @JUEVES AS DATE
DECLARE @VIERNES AS DATE
DECLARE @SABADO AS DATE
DECLARE @FIN_SEMANA AS DATE

SET @INICIO_SEMANA = CAST(DATEADD(wk, DATEDIFF(wk, 0, @FECHA), 0) AS DATE)
SET @MARTES = DATEADD(DAY, 1, @INICIO_SEMANA)
SET @MIERCOLES = DATEADD(DAY, 2, @INICIO_SEMANA)
SET @JUEVES = DATEADD(DAY, 3, @INICIO_SEMANA)
SET @VIERNES = DATEADD(DAY, 4, @INICIO_SEMANA)
SET @SABADO = DATEADD(DAY, 5, @INICIO_SEMANA)
SET @FIN_SEMANA = CAST(DATEADD(wk, DATEDIFF(wk, 0, @FECHA), 6) AS DATE)

SELECT CODIGO
    ,OPERARIO
    ,[@INICIO_SEMANA] AS LUNES
    ,[@MARTES] AS MARTES
    ,[@MIERCOLES] AS MIERCOLES
    ,[@JUEVES] AS JUEVES
    ,[@VIERNES] AS VIERNES
    ,[@SABADO] AS SABADO
    ,[@FIN_SEMANA] AS DOMINGO
FROM (
    SELECT a.OPER_CODIGO CODIGO
        ,a.OPER_NOMBRE + ' ' + a.OPER_APEPATER + ' ' + a.OPER_APEMATER OPERARIO
        ,CAST(c.TCKT_FFINAL AS DATE) FECHAS
        ,COUNT(b.TCKT_NUMERO) CANTIDAD
    FROM OPERARIO a
    INNER JOIN BITACORA b ON a.OPER_CODIGO = b.OPER_CODIGO
    INNER JOIN TICKET c ON b.TCKT_NUMERO = c.TCKT_NUMERO
    WHERE CAST(c.TCKT_FFINAL AS DATE) BETWEEN @INICIO_SEMANA
            AND @FIN_SEMANA
    GROUP BY a.OPER_CODIGO
        ,a.OPER_NOMBRE + ' ' + a.OPER_APEPATER + ' ' + a.OPER_APEMATER
        ,CAST(c.TCKT_FFINAL AS DATE)
    ) z
PIVOT(SUM(CANTIDAD) FOR FECHAS IN (
            [@INICIO_SEMANA]
            ,[@MARTES]
            ,[@MIERCOLES]
            ,[@JUEVES]
            ,[@VIERNES]
            ,[@SABADO]
            ,[@FIN_SEMANA]
            )) AS pvt
ORDER BY pvt.CODIGO ASC
GO

When executing my procedure, SQL throws me the following error:

Mens 8114, Level 16, State 1, Procedure sp_reporteSemana, Line   35 Error converting nvarchar data type to date. Mens 473,

Level 16, State 1, Procedure sp_reporteSemana, Line 35 It has been   provided the incorrect value "@STONE_SEMANA" in the operator PIVOT.

The tables are the following

Where:

TCKT_NUMERO is INT

TCKT_FINAL is DATETIME

OPER_CODIGO INT

The rest of the columns marked VARCHAR

I enclose the script:

link

It's a bit long that's why I uploaded it to my Drive.

sql sql-server stored-procedure pivot

asked by Wil Pedroso 11.01.2017 в 00:54

source

2 answers

protocol error 0x1104 How to disable an option in a select

score 1 · Answer 1

As already mentioned, you can not use variables with PIVOT . But instead of building a dynamic query, you can achieve the pivot equivalent by using the SUM function with a condition by date. Doing it that way, you can use the variables without problem:

CREATE PROCEDURE sp_reporteSemana @FECHA DATE
AS
DECLARE @INICIO_SEMANA AS DATE
DECLARE @MARTES AS DATE
DECLARE @MIERCOLES AS DATE
DECLARE @JUEVES AS DATE
DECLARE @VIERNES AS DATE
DECLARE @SABADO AS DATE
DECLARE @FIN_SEMANA AS DATE

SET @INICIO_SEMANA = CAST(DATEADD(wk, DATEDIFF(wk, 0, @FECHA), 0) AS DATE)
SET @MARTES = DATEADD(DAY, 1, @INICIO_SEMANA)
SET @MIERCOLES = DATEADD(DAY, 2, @INICIO_SEMANA)
SET @JUEVES = DATEADD(DAY, 3, @INICIO_SEMANA)
SET @VIERNES = DATEADD(DAY, 4, @INICIO_SEMANA)
SET @SABADO = DATEADD(DAY, 5, @INICIO_SEMANA)
SET @FIN_SEMANA = CAST(DATEADD(wk, DATEDIFF(wk, 0, @FECHA), 6) AS DATE)

;with cte as (
  SELECT a.OPER_CODIGO CODIGO,
         a.OPER_NOMBRE + ' ' + a.OPER_APEPATER + ' ' + a.OPER_APEMATER OPERARIO,
         CAST(c.TCKT_FFINAL AS DATE) FECHAS,
         COUNT(b.TCKT_NUMERO) CANTIDAD
    FROM OPERARIO a
    JOIN BITACORA b ON a.OPER_CODIGO = b.OPER_CODIGO
    JOIN TICKET c ON b.TCKT_NUMERO = c.TCKT_NUMERO
   WHERE CAST(c.TCKT_FFINAL AS DATE) BETWEEN @INICIO_SEMANA AND @FIN_SEMANA
   GROUP BY a.OPER_CODIGO,
            a.OPER_NOMBRE + ' ' + a.OPER_APEPATER + ' ' + a.OPER_APEMATER,
            CAST(c.TCKT_FFINAL AS DATE)
)
select codigo,
       operario,
       coalesce(sum(case when fechas = @inicio_semana then cantidad end),0) as lunes,
       coalesce(sum(case when fechas = @martes then cantidad end),0) as martes,
       coalesce(sum(case when fechas = @miercoles then cantidad end),0) as miercoles,
       coalesce(sum(case when fechas = @jueves then cantidad end),0) as jueves,
       coalesce(sum(case when fechas = @viernes then cantidad end),0) as viernes,
       coalesce(sum(case when fechas = @sabado then cantidad end),0) as sabado,
       coalesce(sum(case when fechas = @fin_semana then cantidad end),0) as domingo
  from cte
 group by codigo, operario
 order by codigo, operario;
GO

Demo (Thanks to @Flxtr for passing your script to rextester !)

score 0 · Answer 2

This error comes up because in a PIVOT you can not use variables, therefore, it is recommended to build a dynamic query and then execute it with the% EXEC , so that your Stored Procedure do not mark errors should be as follows (although I'm not sure if it is the desired result):

ALTER PROCEDURE sp_reporteSemana @FECHA DATE
AS
DECLARE @INICIO_SEMANA AS DATE
DECLARE @MARTES AS DATE
DECLARE @MIERCOLES AS DATE
DECLARE @JUEVES AS DATE
DECLARE @VIERNES AS DATE
DECLARE @SABADO AS DATE
DECLARE @FIN_SEMANA AS DATE

SET @INICIO_SEMANA = CAST(DATEADD(wk, DATEDIFF(wk, 0, @FECHA), 0) AS DATE)
SET @MARTES = DATEADD(DAY, 1, @INICIO_SEMANA)
SET @MIERCOLES = DATEADD(DAY, 2, @INICIO_SEMANA)
SET @JUEVES = DATEADD(DAY, 3, @INICIO_SEMANA)
SET @VIERNES = DATEADD(DAY, 4, @INICIO_SEMANA)
SET @SABADO = DATEADD(DAY, 5, @INICIO_SEMANA)
SET @FIN_SEMANA = CAST(DATEADD(wk, DATEDIFF(wk, 0, @FECHA), 6) AS DATE)

DECLARE @SQL VARCHAR(MAX)

SET @SQL = '
    SELECT CODIGO
        ,OPERARIO
        ,''' + CAST(@INICIO_SEMANA AS VARCHAR(12)) + ''' AS LUNES
        ,''' + CAST(@MARTES AS VARCHAR(12)) + ''' AS MARTES
        ,''' + CAST(@MIERCOLES AS VARCHAR(12)) + ''' AS MIERCOLES
        ,''' + CAST(@JUEVES AS VARCHAR(12)) + ''' AS JUEVES
        ,''' + CAST(@VIERNES AS VARCHAR(12)) + ''' AS VIERNES
        ,''' + CAST(@SABADO AS VARCHAR(12)) + ''' AS SABADO
        ,''' + CAST(@FIN_SEMANA AS VARCHAR(12)) + ''' AS DOMINGO
    FROM (
        SELECT a.OPER_CODIGO CODIGO
            ,a.OPER_NOMBRE + '' '' + a.OPER_APEPATER + '' '' + a.OPER_APEMATER OPERARIO
            ,CAST(c.TCKT_FFINAL AS DATE) FECHAS
            ,COUNT(b.TCKT_NUMERO) CANTIDAD
        FROM OPERARIO a
        INNER JOIN BITACORA b ON a.OPER_CODIGO = b.OPER_CODIGO
        INNER JOIN TICKET c ON b.TCKT_NUMERO = c.TCKT_NUMERO
        WHERE CAST(c.TCKT_FFINAL AS DATE) BETWEEN ''' + CAST(@INICIO_SEMANA AS VARCHAR(12))+ '''
                AND ''' + CAST(@FIN_SEMANA AS VARCHAR(12)) + '''
        GROUP BY a.OPER_CODIGO
            ,a.OPER_NOMBRE + '' '' + a.OPER_APEPATER + '' '' + a.OPER_APEMATER
            ,CAST(c.TCKT_FFINAL AS DATE)
        ) z
    PIVOT(SUM(CANTIDAD) FOR FECHAS IN (
                 [' + CAST(@INICIO_SEMANA AS VARCHAR(12)) + ']
                ,[' + CAST(@MARTES AS VARCHAR(12)) + ']
                ,[' + CAST(@MIERCOLES AS VARCHAR(12)) + ']
                ,[' + CAST(@JUEVES AS VARCHAR(12)) + ']
                ,[' + CAST(@VIERNES AS VARCHAR(12)) + ']
                ,[' + CAST(@SABADO AS VARCHAR(12)) + ']
                ,[' + CAST(@FIN_SEMANA AS VARCHAR(12)) + ']
                )) AS pvt
    ORDER BY pvt.CODIGO ASC
'
EXEC(@SQL)
GO