CODEWITHSUNDEEP
sqlsql-server
A.A
A.A

Reputation: 91

Conversion failed when converting date and/or time from character string month(),year()

I have these data:

Col1             Col2  Col3
2014-12-31        Q     
2014-12-10        Q


CREATE TABLE Persons
(
    Col1 varchar(50),
    Col2 varchar(50),
    Col3 varchar(20)
); 

INSERT INTO Persons(Col1, Col2,Col3) 
VALUES ('2014-12-20', 'Q','');

INSERT INTO Persons(Col1, Col2,Col3) 
VALUES ('2014-12-10', 'Q','');

I run this query:

select 
  convert(date, Col1,104) as Col1,
  Col2,
  case when Col2 = 'Q' then convert(nvarchar(10),year(Col1)+'Q0')
  end as Col3
from Persons

I get this error:

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

Upvotes: 2

Views: 1001

Answers (3)

Joy Acharya
Joy Acharya

Reputation: 680

DECLARE  @P TABLE
(

    Col1 varchar(50),
    Col2 varchar(50),
    Col3 varchar(20)
) 

  INSERT INTO @P(Col1, Col2,Col3)    
  VALUES ('2014-12-20', 'Q','');

  INSERT INTO @P(Col1, Col2,Col3)    
  VALUES ('2014-12-10', 'Q','');

select 
  convert(varchar(10), Col1,104) as Col1,
  Col2,
  case when Col2 = 'Q' then
    RIGHT(convert(nvarchar(10),year(Col1),106),5) + 'Q0'
  end as Col3    
from @P

OUTPUT:

enter image description here

Upvotes: 1

Matej Hlavaj
Matej Hlavaj

Reputation: 1038

I see there 2 problems. First one is with convert to concrete date format from varchar. At the first , convert it only in to date format. Then use concrete format. Problem number 2 is in Col3. At the first you must convert year on the varchar, then use + with string. Solution:

select 
  CONVERT(DATE,convert(date, Col1),104) as Col1,
  Col2,
  case when Col2 = 'Q' then convert(nvarchar(10),year(Col1))+'Q0'
  end as Col3
from Persons

or

select 
  CONVERT(DATE,convert(date, Col1),104) as Col1,
  Col2,
  case when Col2 = 'Q' then convert(nvarchar(10),year(Col1))+'Q0'
  end as Col3
from Persons
WHERE isdate(Col1) = 1

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

Your error is caused by:

  1. convert(date, Col1, 104)

Note that 104 is dd.mm.yyyy. '2014-12-20' will result in an error:

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

You need to cast it to date first then convert to varchar:

SELECT CONVERT(VARCHAR(10), CAST('2014-12-20' AS DATE), 104)

which outputs: 20.12.2014.

  1. convert(nvarchar(10),year(Col1)+'Q0')

You should convert year(Col1) to varchar first before concatenating 'Q0'.

Your final query should be:

SELECT
    CONVERT(NVARCHAR(10), CAST(Col1 AS DATE), 104) AS Col1,
    Col2,
    CASE 
        WHEN Col2 = 'Q' THEN CONVERT(NVARCHAR(10), CONVERT(NVARCHAR(4), YEAR(Col1)) + 'Q0')
    END AS Col3
FROM Persons

You should also convert Col1 to DATE first before calling YEAR(). As stated in the comments, the problem starts when you decided to store dates in a nvarchar column. The CAST to DATE will still error out if the Col1 is an invalid date.

Upvotes: 0

Related Questions