CODEWITHSUNDEEP
sqlsql-server
Rohan Rao
Rohan Rao

Reputation: 2603

Cannot convert the datetime to char in sql

I have the following query in SQL:

SELECT DISTINCT Id,Name,Date, WeekOffDate 
FROM tblEmployee 
LEFT JOIN tblWeekOff

So clearly, Id and Name come from tblEmployee and WeekOffDate comes from tblWeekOff.

Now, in the Date column, I am writing this piece of query:

SELECT DISTINCT 
    Id, Name, 
    (CASE WHEN Date = WeekOffDate THEN 'WO' END) 
FROM tblEmployee

But I get this error saying:

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

So I to overcome this error, I tried this

SELECT DISTINCT 
    Id, Name, 
    (CASE WHEN CAST(Date AS nvarchar) = CAST(WeekOffDate AS nvarchar) THEN 'WO')
FROM tblEmployee

i.e. I tried casting. Similarly I tried CONVERT() function, but no luck.

My goal is to show the "WO" status where the date matches with the WeekOffDate in a table. But I am really surprised why is this happening?

EDIT:

The WeekOffDate data looks like this:

WeekOffDate

Where am I lacking?

Upvotes: 0

Views: 186

Answers (1)

ismetguzelgun
ismetguzelgun

Reputation: 1105

Could you try this. 

(CASE WHEN CONVERT(NVARCHAR(10), CONVERT(DATETIME, CONVERT(DATE, DATE)) , 103) = CONVERT(NVARCHAR(10), CONVERT(DATETIME, CONVERT(DATE, WeekOffDate))  , 103) THEN 'WO' ELSE 'YES'END)

EDIT: Warning is nothing more than a warning in this case. It is just saying the fact. You can overcome this with adding an else statement in your case.

Upvotes: 1

Related Questions