Using IS NULL to convert NULL dates

Question

I'm running a query to change some returned data around in SQL Server 2016, and I am getting a conversion error (Conversion failed when converting date and/or time from character string).

SELECT jobs.name,
       CASE WHEN jact.start_execution_date IS NULL THEN 'No Execution'
            ELSE jact.start_exeuction_date
       END AS start_execution_date
FROM   msdb.dbo.sysjobactivity jact
       INNER JOIN msdb.dbo.sysjobs jobs
         ON jact.job_id = jobs.job_id

The start_execution_date column is a DATETIME type. This column either has a datetime format of (YYYY-MM-DD HH:MM:SS.mmm) or NULL in it. I even tried using COALESCE to make the code a little easier to read but that also did not work and gave the same error. When viewing the returned result set up to the point of the error, it is failing at the first row that has a NULL in this column. What am I doing wrong/not considering?

SELECT COALESCE(jact.start_execution_date, 'No Execution')

you can't use `case` with different return types..varchar and date in your case. — Vamsi Prabhala, Jun 07 '17 at 12:42

score 1 · Answer 1 · answered Jun 07 '17 at 12:46

Your result set (the output of this query) has to have the same field type in each field. It's like a derived table, so you can't have a varchar 'No Execution' residing in the same field as your date from jact.start_execution_date.

However, you can cast the date as a VARCHAR() like:

COALESCE(CAST(jact.start_execution_date AS VARCHAR(20), 'No Execution') as start_execution_date

Just know that your date is now a string date, which is fine if this is being outputted somewhere.

score 1 · Accepted Answer · answered Jun 07 '17 at 12:46

Presumably, start_execution_date is a date. So, you need to convert it. The simplest way is:

SELECT . . .,
       COALESCE(CONVERT(VARCHAR(255), jact.start_execution_date), 
                'No Execution'
               ) as start_execution_date
FROM msdb.dbo.sysjobactivity jact INNER JOIN
     msdb.dbo.sysjobs jobs
     ON jact.job_id = jobs.job_id;

You can add a third parameter that specifies the format of the result. Or, use the FORMAT() function itself.

Using IS NULL to convert NULL dates

2 Answers2