0

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!

Sergio
  • 28,539
  • 11
  • 85
  • 132
24601
  • 23
  • 1
  • 4

1 Answers1

3

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"
Andriy M
  • 76,112
  • 17
  • 94
  • 154
  • +1 I'd only suggest `[brackets]` are better for aliases than `"double quotes"` simply because many people confuse those as strings. – Aaron Bertrand Jun 23 '12 at 19:28
  • Yes... seeing it now makes much clearer sense. And IT WORKED - thanks for your time and your clarification! – 24601 Jun 25 '12 at 14:35