Search code examples
sqladventureworks

SQL Function Error Converting null date to String

I am using Adventureworks2008R2 Database and trying to render the following. I have problem with Case clause in the SQL Query.

Enddate in the table is of date datatype

SELECT  
         Firstname,
            Lastname, 
            Startdate,
            'END DATE' =
              CASE   
                WHEN (ENDDATE is  null) Then 'Still Working'
                ELSE EndDate
               END
FROM 
     DIMEMPLOYEE

This is the error I get.

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

I know why it is coming but don't know easy fix? Update:

What all I want is When endate is not null then replace that column with 'still Working' String.

Solution

  • Would place a CAST around the EndDate column help you here. I think that the CASE statement is defining the column with a string datatype.

    CASE
WHEN (EndDate IS NULL) THEN 'Still working'
ELSE CAST(EndDate AS VARCHAR)
END