Getting an error Conversion failed when converting date and/or time from character string

Question

The query shown below is causing an error

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

Please help me

SELECT 
    a.[Prod_id], a.[ctry_id], a.[ctry_cd],
    left(a.[re_ver],8) + ' (Per ' + a.[re_ver] + ')' as [re_ver],
    'YTD' as [per_type],
    sum(b.[unt_cas_cy_bp]) [unt_cas_cy_bp],
    sum(b.[unt_cas_cy_re]) [unt_cas_cy_re],
    sum(b.[unt_cas_py_act]) [unt_cas_py_act],
    sum(b.[unt_cas_2py_act]) [unt_cas_2py_act]
FROM  
    a
INNER JOIN
    b ON a.Prod_id = b.Prod_id 
      AND a.ctry_id = b.ctry_id 
      AND a.ctry_cd = b.ctry_cd
      AND b.per_type = 'Monthly'
      AND YEAR(LEFT(b.re_ver, 8)) * 100 + MONTH(LEFT(b.re_ver, 8)) 
              BETWEEN YEAR(LEFT(a.re_ver, 8)) * 100 + 1 
                  AND YEAR(LEFT(a.re_ver, 8)) * 100 + MONTH(LEFT(a.re_ver, 8)) 
WHERE
    a.per_type = 'Yearly'
GROUP BY
    a.[Prod_id], a.[ctry_id], a.[ctry_cd],
    a.[re_ver], a.[per_type]

it's going to be on this part AND YEAR(LEFT(b.re_ver, 8)) * 100 + MONTH(LEFT(b.re_ver, 8)) BETWEEN YEAR(LEFT(a.re_ver, 8)) * 100 + 1 AND YEAR(LEFT(a.re_ver, 8)) * 100 + MONTH(LEFT(a.re_ver, 8)) but you 'll need to show us what is in those columns... i..e give us some sample data — S3S
the data is like Feb 2017,Q4 2017 and declared as varchar data type — user7449410
select YEAR(LEFT(b.re_ver, 8)) if i do this will get only the year like 2017. then *100 will get 201700 and +1 means 201701 — user7449410

S3S S3S · Accepted Answer · 2017-12-22T17:27:20

The problem is your abbreviations can be 3 or 4 characters long. i.e. September is abbreviated Sept. You are trying to create a date column from this varchar field in an odd way, and it's not sargable. Instead, I would do the conversion in a CTE, and then use it in your where clause.

declare @badData table (re_ver varchar(64))
insert into @badData
values
('Feb 2017,Q4 2017'),
('Sept 2017,Q3 2016')

;with cte as(
select
    re_ver
    ,re_ver_date =  cast(
                        cast(case 
                            when left(re_ver,4) = 'Jan' then 1
                            when left(re_ver,4) = 'Feb' then 2
                            when left(re_ver,4) = 'Mar' then 3
                            when left(re_ver,4) = 'Apr' then 4
                            when left(re_ver,4) = 'May' then 5
                            when left(re_ver,4) = 'Jun' then 6
                            when left(re_ver,4) = 'Jul' then 7
                            when left(re_ver,4) = 'Aug' then 8
                            when left(re_ver,4) = 'Sept' then 9
                            when left(re_ver,4) = 'Oct' then 10
                            when left(re_ver,4) = 'Nov' then 11
                            when left(re_ver,4) = 'Dec' then 12
                        end as varchar(2))
                        + '/' 
                        + '01' 
                        + '/'
                        + cast(right(substring(re_ver,1,charindex(',',re_ver) - 1),4) as char(4))
                    as date)
from
    @badData)

select * 
from cte
--where <your condition>

I'm getting the year from the 'Mon YYYY' part, not the Q# YYYY part in the query. However, if you want the year from the quarter part, just change the last line to:

cast(right(re_ver,4) as char(4))

Getting an error Conversion failed when converting date and/or time from character string

1 Answers