With the following Query (wich contains no string to date and/or time conversion) :

SELECT
    Date,
    RangeFam,
    QTY, 
    Country


FROM (
            SELECT  Q_QTM       as Qty,
                    Q_OFDFIN    as Date,
                    Q_PAYS      as Country,
                    CASE Q_DTRI05 
                        WHEN 'eBALTIC - EBB'        THEN 'EBAB'
                        WHEN 'Baltic -'             THEN 'BALT'
                        WHEN 'eBALTIC - EBF'        THEN 'EBAF'
                        WHEN 'Roof-tops - ENERGY'   THEN 'ENER'
                        WHEN 'Roof-tops - FLEXAIR'  THEN 'FLER'
                    END         as RangeFam

            FROM [sales].[dbo].[CARNET2_D]
            WHERE   Q_OFET='D' 
                    and Q_TYPE='M'
    
    UNION ALL
        
            SELECT  Qty, 
                    Range_Code as RangeFam,
                    ShippingDate as Date,
                    CASE Country collate DATABASE_DEFAULT
                        WHEN 'France'       THEN 'FRANCE'
                        WHEN 'Netherlands'  THEN 'PAYS BAS'
                        WHEN 'Belgium'      THEN 'BELGIQUE'
                        WHEN 'Portugal'     THEN 'PORTUGAL'
                        WHEN 'Spain'        THEN 'ESPAGNE'
                    END AS Country

            FROM [crmv2].[dbo].[View_Forecastdata_Extended]
            WHERE   BaseUnit='1' 
                    and (Project_status='Detection / Faisability' or  Project_status='Execution' or Project_status='Quotation' or Project_status='Specification stage')
                    and [Report_S3A/B_group]='Rooftop' 
                    and Sop='1' 
                    and SopValid='1'
                    and (Sourcing='L&B' or Sourcing='LON' or Sourcing='BUR')
        ) as T1

ORDER BY Date

I get this error :

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

Something weird is that if I remove all the code related to the field RangeFam the query is ok so I guess I miss something about it

Thank you

When I say that when i remove the code about RangeFam I mean that there is no problem with the following Query :

SELECT
    Date,
    --RangeFam,
    QTY, 
    Country


FROM (
            SELECT  Q_QTM       as Qty,
                    Q_OFDFIN    as Date,
                    Q_PAYS      as Country
                    --CASE Q_DTRI05 
                    --  WHEN 'eBALTIC - EBB'        THEN 'EBAB'
                    --  WHEN 'Baltic -'             THEN 'BALT'
                    --  WHEN 'eBALTIC - EBF'        THEN 'EBAF'
                    --  WHEN 'Roof-tops - ENERGY'   THEN 'ENER'
                    --  WHEN 'Roof-tops - FLEXAIR'  THEN 'FLER'
                    --END           as RangeFam

            FROM [sales].[dbo].[CARNET2_D]
            WHERE   Q_OFET='D' 
                    and Q_TYPE='M'
    
    UNION ALL
        
            SELECT  Qty, 
                    --Range_Code as RangeFam,
                    ShippingDate as Date,
                    CASE Country collate DATABASE_DEFAULT
                        WHEN 'France'       THEN 'FRANCE'
                        WHEN 'Netherlands'  THEN 'PAYS BAS'
                        WHEN 'Belgium'      THEN 'BELGIQUE'
                        WHEN 'Portugal'     THEN 'PORTUGAL'
                        WHEN 'Spain'        THEN 'ESPAGNE'
                    END AS Country

            FROM [crmv2].[dbo].[View_Forecastdata_Extended]
            WHERE   BaseUnit='1' 
                    and (Project_status='Detection / Faisability' or  Project_status='Execution' or Project_status='Quotation' or Project_status='Specification stage')
                    and [Report_S3A/B_group]='Rooftop' 
                    and Sop='1' 
                    and SopValid='1'
                    and (Sourcing='L&B' or Sourcing='LON' or Sourcing='BUR')
        ) as T1

ORDER BY Date

CodePudding user response：

The way the query is written the contents of Q_OFDFIN in the first subquery are UNIONed with the contents of Range_Code in the second one. Since one seems to be a Date and the other a string, the server has to convert them to a common type.

UNION and UNION ALL work on result fields by position, not subquery aliases.

To fix the problem ensure the columns appear in the correct order:

FROM (
            SELECT  Q_QTM       as Qty,
                    Q_OFDFIN    as Date,
                    Q_PAYS      as Country,
                    CASE Q_DTRI05 
                        WHEN 'eBALTIC - EBB'        THEN 'EBAB'
                        WHEN 'Baltic -'             THEN 'BALT'
                        WHEN 'eBALTIC - EBF'        THEN 'EBAF'
                        WHEN 'Roof-tops - ENERGY'   THEN 'ENER'
                        WHEN 'Roof-tops - FLEXAIR'  THEN 'FLER'
                    END         as RangeFam

            FROM [sales].[dbo].[CARNET2_D]
            WHERE   Q_OFET='D' 
                    and Q_TYPE='M'
    
    UNION ALL
        
            SELECT  Qty, 
                    ShippingDate as Date,
                    CASE Country collate DATABASE_DEFAULT
                        WHEN 'France'       THEN 'FRANCE'
                        WHEN 'Netherlands'  THEN 'PAYS BAS'
                        WHEN 'Belgium'      THEN 'BELGIQUE'
                        WHEN 'Portugal'     THEN 'PORTUGAL'
                        WHEN 'Spain'        THEN 'ESPAGNE'
                    END AS Country,
                    Range_Code as RangeFam
            FROM [crmv2].[dbo].[View_Forecastdata_Extended] 
...