SQL query w/ ISNULL won't output smalldatetime to 101 format

Question

Snippet from a SQL query (using SQL Server 2008 R2):

, CONVERT (VARCHAR(10), s.ENTER_DATE, 101) "Enrollment Start Date"

, CONVERT (VARCHAR(10), (ISNULL(CAST(s.LEAVE_DATE AS VARCHAR(10)), '')) ,101) "Enrollment Drop Date"

The top line works fine to output the date to proper 101 format (eg: 06/22/2012).

The bottom line works as desired to remove any NULL values to a blank, but... for any date values it does not output that date to proper 101 format, instead outputting the date in the default format (eg: Jun 22 2012).

I've played with the code and searched online but I cannot get the desired result.

Can anyone suggest a solution?

Thank you!

Answer 1

Your second expression evaluates like this:

1. A date or datetime value is converted to a string (using CAST())

2. ISNULL() is applied which returns the same result (the same string) if it is not NULL.

3. The string is converted to... a string (using CONVERT() this time).

So, as you can see, you get the wrongly formatted date as a result of the innermost conversion.

You just need to apply ISNULL differently, I think. Probably like this:

ISNULL(CONVERT (VARCHAR(10), s.ENTER_DATE, 101), '') "Enrollment Start Date"