SQLServer DATETIME conversion to STRING if NULL

Question

my datetime column (type=datetime) in my DB is as follows

enter image description here

why is this statement not converting the '1900-01-01 00:00:00.000' values to 'date not set'

ISNULL(
   CASE WHEN CONVERT(VARCHAR, [TB].Date) = '1900-01-01 00:00:00.000' 
   THEN 'date not set' 
   ELSE CONVERT(DATE, [TB].Date, 25) 
   END, '') AS Xdate,

What am i missing?

Cheers Paul

Slightly OT so I'll post this as a comment rather than an answer: Since "1900-01-01 00:00:00.000" is the baseline value for the DATETIME type, you could simplify to something like CASE WHEN [TB_EVNTCERT].StartDate = 0 THEN ... ELSE ... END — LukeH
– LukeH, Commented Jun 30, 2015 at 16:01

Brian Pressler · Accepted Answer · 2015-06-30 16:31:26Z

2

If you try this code you will see that when you convert it to varchar it does not equal the string you are comparing it to:

declare @Startdate as datetime
set @Startdate='1900-01-01 00:00:00.000'

select CONVERT(VARCHAR, @StartDate)

You want to compare the datetime types. And you need to output the date as a varchar because you also want to output 'date not set' which is not a date. You can't conditionally output a different type... only one or the other can be the type of your case statement.

So you need to swap the date/varchar conversions in your expression:

ISNULL(
   CASE WHEN CONVERT(DATE, [TB_EVNTCERT].StartDate) = '1900-01-01 00:00:00.000' THEN
       'date not set' 
   ELSE 
       CONVERT(VARCHAR, [TB_EVNTCERT].StartDate, 25) 
   END, '') AS expirydate,

edited Jun 30, 2015 at 16:31

answered Jun 30, 2015 at 15:52

Brian Pressler

6,7232 gold badges21 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tab Alleman · Accepted Answer · 2015-06-30 15:51:39Z

2

Your CASE statement cannot work. The result of the CASE statement must be a single datatype. You are trying to return a varchar ("date not set") in one case, and a DATE in another case.

You must return the same datatype in all cases.

answered Jun 30, 2015 at 15:51

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Collectives™ on Stack Overflow

SQLServer DATETIME conversion to STRING if NULL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related