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

Lucca Clemente

So, I need to show the consumption of the material on the current month and the last 6 too. I'm trying to put an alias name on the columns with the respective month just like "MM/YY".

Example of the columns: 08/17 | 07/17 | 06/17 | ... | 02/17

DECLARE @mes_atual DATE; SET @mes_atual = CAST(FORMAT(GETDATE(),'MM/YY') AS DATE)
    DECLARE @mes1 DATE; SET @mes1 = DATEADD(MM, -1, @mes_atual); SET @mes1 = CAST(@mes1 AS VARCHAR)
    DECLARE @mes2 DATE; SET @mes2 = DATEADD(MM, -2, @mes_atual); SET @mes2 = CAST(@mes2 AS VARCHAR)
    DECLARE @mes3 DATE; SET @mes3 = DATEADD(MM, -3, @mes_atual); SET @mes3 = CAST(@mes3 AS VARCHAR)
    DECLARE @mes4 DATE; SET @mes4 = DATEADD(MM, -4, @mes_atual); SET @mes4 = CAST(@mes4 AS VARCHAR)
    DECLARE @mes5 DATE; SET @mes5 = DATEADD(MM, -5, @mes_atual); SET @mes5 = CAST(@mes5 AS VARCHAR)
    DECLARE @mes6 DATE; SET @mes6 = DATEADD(MM, -6, @mes_atual); SET @mes6 = CAST(@mes6 AS VARCHAR)
    SET @mes_atual = CAST(@mes_atual AS VARCHAR)
    DECLARE @query VARCHAR;
    DECLARE @selects VARCHAR;

    SET @selects = 'QT_CONS_MES_ATUAL AS [' + CAST(@mes_atual AS VARCHAR) + '], QT_CONS_MES_1 AS [' + CAST(@mes1 AS VARCHAR) + '], QT_CONS_MES_2 AS [' + CAST(@mes2 AS VARCHAR) + '], QT_CONS_MES_3 AS [' + CAST(@mes3 AS VARCHAR) + '], QT_CONS_MES_4 AS [' + CAST(@mes4 AS VARCHAR) + '], QT_CONS_MES_5 AS [' + CAST(@mes5 AS VARCHAR) + '], QT_CONS_MES_6 AS [' + CAST(@mes6 AS VARCHAR) + ']'

    SET @query = 'SELECT [VERSAO]
          ,[IN_TP_DISP]
          ,[CD_OWNER]
          ,[DS_OWNER]
          ,[OP_ITEM_PROD]
          ,[DS_PRODUTO]
          ,[CD_MAT_GRUPO]
          ,[CD_MAT_SUBGRUPO]
          ,[CD_MAT_CLASSE]
          ,[CD_MAT_NIVEL4]
          ,[CD_MAT_NIVEL5]
          ,[CD_UNMED_EST]
          ,[CD_DIVISAO]
          ,[IN_CTR_DISP]
          ,[IN_TIPO_REPOS]
          ,[QT_ETQ_MAXIMO]
          ,[QT_ETQ_MINIMO]
          ,[QT_LOTE_COMPRA]
          ,[QT_SALDO_ATUAL]
          ,[VL_SALDO_ATUAL]
          ,[QT_SALDO_RES]
          ,[QT_SALDO_ENC]
          ,[QT_SALDO_DISP]
          ,'+@selects+'
          ,[SQ_MOVTO]
          ,[QT_SALDO_CUR]
          ,[QT_SALDO_BENTO]
          ,[QT_SALDO_BAURU]
          ,[CD_FOR_ITEM]
          ,[VL_CUSTO_ULT_COMPRA]
          ,[CD_PESSOA_FORN]
          ,[NOME_FORN]
          ,[AN_NFE]
          ,[DT_ENTRADA]
          ,[AN_PC]
      FROM [dbo].[VW_SQL_CONSUMO_BI]'

    EXEC(@query)

I'm trying to put the alias name of some columns with a dinamic date but when I convert the date to a VARCHAR data, build the query and exec it i get the error:

Mensagem 241, Nível 16, Estado 1, Linha 3
Conversion failed when converting date and/or time from character string.

Ps: The error message is in portuguese but I hope u guys can help me.

GuidoG

you cannot create a valid date without a day So put GetDate in your variable and use Format to only show the year and month

DECLARE @mes_atual DATE 
SET @mes_atual = GetDate()

select @mes_atual as mes_atual,
       FORMAT(@mes_atual,'MM/yy') AS date

this returns

mes_atual   date    
2017-08-04  08/17   

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

conversion failed when converting date and/or time from character string in xml

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

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

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

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

Conversion failed when converting date and/or time from character string - why?

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

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

DateTime conversion failed when converting date and/or time from character string

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

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

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

Conversion failed when converting date and/or time from character string when converting inputted time | SQL, SQLSRV

SQL Server: passing string from variable into date - Conversion failed when converting date and/or time from character string

STRING Date to DATETIME - Conversion failed when converting date and/or time from character string while inserting datetime

SQL Error "Conversion failed when converting date and/or time from character string" whereas not converting

Conversion Failed from converting Date/time from character String

SQL Server Conversion failed when converting from date and/or time from character string when query is parameterized

two date comparisn gives Conversion failed when converting date and/or time from character string

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

Inserting or Updating a Date field give the following error Conversion failed when converting date and/or time from character string

Conversion failed when converting date and/or time from character string. when filtering sql select query

Conversion failed when converting date and/or time from character string,when convert hours

Conversion failed when converting date and/or time from character string When using parameterized query

Conversion failed when converting date and/or time from character string when using the EXEC command

T SQL Conversion failed when converting date and/or time from character string from VARCHAR(MAX)

Conversion failed when converting date from character string in sql server

How to circumvent error Message 241 (Conversion failed when converting date and/or time from character string)

Conversion failed when converting date and/or time from character string (Return the last day of month)