SQL Server : converting Null date times

Question

I have a query in SSMS which is returning 1900-01-01, how can I use a CASE WHEN accurately to replace 1900-01-01 with '' (a blank not a null).

CAST(ISNULL(CAST(CONVERT(DATE, cmmt.[CmmtExpirationDate], 101) AS NVARCHAR(20)), '') AS DATE) AS [Cmmt Expiration Date]

Result: 1900-01-01

I tried this but no luck (terrible syntax):

CASE 
   WHEN (CAST(ISNULL(cast(convert(Date, cmmt.[CmmtExpirationDate] , 101) as nvarchar(20)), '') = '1900-01-01')
      THEN ''
      ELSE CAST(ISNULL(cast(convert(Date, cmmt.[CmmtExpirationDate] , 101) as nvarchar(20)),'') AS DATE
END

You can't. An empty string will force an implicit conversion to a datetime which is 1900-01-01. You have to remember that the datatype here is a date and an empty string is NOT a valid date. If you want to display an empty string when there is no date this should be done in the front end. — Sean Lange
– Sean Lange, Commented Aug 31, 2016 at 20:47
but you are trying to convert it to a DATE after all, and you can't have an empty string as a DATE (it converts it to 1900-01-01) — Lamak
– Lamak, Commented Aug 31, 2016 at 20:48
Why not return NULL when there is no date instead of empty string. This way you wouldn't mess with return type or "with 1900-01-01" . Just handle null value on client side properly. — AXMIM
– AXMIM, Commented Aug 31, 2016 at 21:02
@Heinzi The data type is listed in the table as [CmmtExpirationDate] [smalldatetime] NULL — Mike
– Mike, Commented Aug 31, 2016 at 21:25

Heinzi · Accepted Answer · 2016-08-31 21:02:26Z

2

The result of your expression needs to have a fixed data type. DATE is not possible, since '' is not a valid date. nvarchar(20) would be an option, but that means that your result will be a string even if it is not 1900-01-01.

Once you accept that, the solution is simple:

CASE WHEN cmmt.[CmmtExpirationDate] = '1900-01-01'
     THEN CONVERT(nvarchar(20), cmmt.[CmmtExpirationDate])
     ELSE CONVERT(nvarchar(20), '')
END

You might want to specify the desired output format as a third parameter to the first CONVERT statement.

(I assume that CmmtExpirationDate is of type DATE, because if it isn't, it should have been mentioned in the question.)

edited Aug 31, 2016 at 21:02

answered Aug 31, 2016 at 20:59

Heinzi

173k61 gold badges386 silver badges555 bronze badges

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

4 Comments

Lamak Over a year ago

It most likely isn't since the op is doing CONVERT(DATE, cmmt.[CmmtExpirationDate], 101)

Sean Lange Over a year ago

I would use caution if converting dates to a string like this. If you do sorting on the client side you won't be able to accurately sort depending on the format of your date strings.

Heinzi Over a year ago

@Lamak: Well, if it's already a string, the solution is trivial CASE WHEN ... = '1900-01-01' THEN ... ELSE '' END.

Heinzi Over a year ago

@SeanLange: Yes. Obviously, returning a date and doing all the formatting on the client side is always preferable. Since I don't know the OP's use case, I assumed "good faith" and just answered the question as stated.

Kannan Kandasamy · Accepted Answer · 2016-08-31 21:11:16Z

0

You can use try_convert as below:

try_convert(date, case when datecolumn='' then null else datecolumn end)

answered Aug 31, 2016 at 21:11

Kannan Kandasamy

14k3 gold badges28 silver badges40 bronze badges

Collectives™ on Stack Overflow

SQL Server : converting Null date times

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related