Problems with a case statement

Transact-SQL
1

I have a field that I want to do a simple CASE statement on, if the field has a certain date, then return as a certain text, if no then show as whatever the date is. Below is my STATEMENT that runs, but still shows 1/1/1900 instead of Not Confirmed.

Thank you.

CASE
WHEN
SOITEM_EXT.FIELD3 LIKE '%1/1/1900%' then 'Not Confirmed'
ELSE SOITEM_EXT.FIELD3
END AS [Promised Date],

2

What is the data type of Field3? If it is a date type then you will need to convert it to a character type (within the CASE statement) for Promised Date.

3

yes, date type.

4

okay I got this to work:
CASE
WHEN SOITEM_EXT.FIELD3 = '1/1/1900' THEN CAST('Not Confirmed' AS Varchar(20))
ELSE CAST(SOITEM_EXT.FIELD3 AS VARCHAR(20))
END AS [Promised Date],

but the date fields are coming in as

Aug 5 2016 instead of 08/05/2016, don't see where I can change that.

Thanks!

5

You need to use CONVERT (VARCHAR(20), SOITEM_EXT.FIELD3, 101) this gives month/day/year

6

thank you!