Null Date returning invalid date

Question

Trying to change NULLs in a date field into blank results but it keeps returning a date. I'm doing it in a case when as i need to meet the following criteria:

CASE WHEN closed = 'y' THEN Date ELSE '' END

I've tried casting as varchar and other things it either throws a error or does nothing!

Are you really trying to return a character value? As in, what is happening to the date that is returned - is it just being displayed or something else that is equivalent to a character value? — Joe, May 29 '13 at 15:05
There is no such thing as a blank date. You either have NULL or a date value. Where did you try casting it as a varchar? — BlargleMonster, May 29 '13 at 15:05

score 1 · Accepted Answer · edited May 23 '17 at 12:18

1

You should convert it to a varchar:

CASE WHEN closed = 'y' THEN LEFT(CONVERT(VARCHAR, Date, 120), 10) ELSE '' END

See How to convert DateTime to VarChar for more information.

edited May 23 '17 at 12:18

Community

1
1

answered May 29 '13 at 15:07

Joe

62,789
6
49
67

score 1 · Answer 2 · answered May 29 '13 at 15:07

1

A column in your result can only contain one type, it can't be both a date and a text field. You could convert your date to a string providing you no longer need it to be a date like so:

CASE WHEN closed = 'y' 
THEN CAST(Date AS VARCHAR) 
ELSE '' END

answered May 29 '13 at 15:07

Dave Sexton

10,768
3
42
56

Null Date returning invalid date

2 Answers2