Data to string conversion in SQL SERVER

Asked

Viewed 141 times

3

I need to perform a conversion of a query of type date to type string, but it always returns me the following error:

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.

Follow the source code:

SELECT DISTINCT V_AssetParentAssetCL.asset, AssetWorkProcedure.equipment, Asset.equipmentType, AssetWorkProcedure.workProcedure, AssetWorkProcedure.intervalType,AssetWorkProcedure.dateInterval, CONVERT(VARCHAR,AssetWorkProcedure.dateInterval) + CONVERT(VARCHAR(1),(CASE WHEN AssetWorkProcedure.intervalType = '2' THEN 'M' ELSE 'D' END)) as period, 

        CASE WHEN (intervalType = '1' OR DATEDIFF(MONTH,initDate,'" + year +"-04-01')%dateInterval = 0) AND (CONVERT(VARCHAR(7),initDate,121) =  '" + year +"-04' OR initDate < '" + year +"-04-01') THEN 'X' ELSE NULL END as ABR,
        CASE WHEN (intervalType = '1' OR DATEDIFF(MONTH,initDate,'" + year +"-05-01')%dateInterval = 0) AND (CONVERT(VARCHAR(7),initDate,121) =  '" + year +"-05' OR initDate < '" + year +"-05-01') THEN 'X' ELSE NULL END as MAI
        FROM Asset
        inner join V_AssetParentAssetCL ON Asset.asset = V_AssetParentAssetCL.asset
        inner join AssetWorkProcedure  ON Asset.asset = AssetWorkProcedure.asset

    AND V_AssetParentAssetCL.parentAsset = 'F.SP.SPO.IP'
    AND AssetWorkProcedure.recordState = 'OP'
    AND AssetWorkProcedure.recordState = 'OP'

Someone can help me ?

sql

  • Change '" + year +"-04-01 for year + '-04-01'

1 answer

1


  1. First to catch the current year use YEAR(GETDATE());
  2. And to concatenate the year, which is an integer with a string, you need to convert the year to scan.

The whole code would look like this:

SELECT DISTINCT V_AssetParentAssetCL.asset, AssetWorkProcedure.equipment, Asset.equipmentType, AssetWorkProcedure.workProcedure, AssetWorkProcedure.intervalType,AssetWorkProcedure.dateInterval, CONVERT(VARCHAR,AssetWorkProcedure.dateInterval) + CONVERT(VARCHAR(1),(CASE WHEN AssetWorkProcedure.intervalType = '2' THEN 'M' ELSE 'D' END)) as period, 

    CASE WHEN (@intervalType = '1' OR DATEDIFF(MONTH, @initDate, CONVERT(VARCHAR, YEAR(GETDATE())) + '-04-01') % @dateInterval = 0) AND (CONVERT(VARCHAR(7), @initDate, 121) =  CONVERT(VARCHAR, YEAR(GETDATE())) + '-04' OR @initDate < CONVERT(VARCHAR, YEAR(GETDATE())) + '-04-01') THEN 'X' ELSE NULL END as ABR,
    CASE WHEN (@intervalType = '1' OR DATEDIFF(MONTH, @initDate, CONVERT(VARCHAR, YEAR(GETDATE())) + '-05-01') % @dateInterval = 0) AND (CONVERT(VARCHAR(7), @initDate, 121) =  CONVERT(VARCHAR, YEAR(GETDATE())) + '-05' OR @initDate < CONVERT(VARCHAR, YEAR(GETDATE())) + '-05-01') THEN 'X' ELSE NULL END as MAI
    FROM Asset
    inner join V_AssetParentAssetCL ON Asset.asset = V_AssetParentAssetCL.asset
    inner join AssetWorkProcedure  ON Asset.asset = AssetWorkProcedure.asset

AND V_AssetParentAssetCL.parentAsset = 'F.SP.SPO.IP'
AND AssetWorkProcedure.recordState = 'OP'
AND AssetWorkProcedure.recordState = 'OP'

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.