How do I code NULL Dates as blank in MS SQL

Question

I have the following tables with column/row values:

tbl1
ID
1
2
3

tbl2
ID    Color    DateRegister
1     'Red'     '2021-02-04 00:00:00.000'
3     'Blue'    '2021-02-04 00:00:00.000'

I'm trying to write a SQL code that selects records from tbl1 with tbl2 via LEFT JOIN. If there is no connected records between tbl1 and tbl2, the tbl2 columns should be NULL. I use CASE to display blank strings for the NULL values. This does not work for the date column.

SELECT
tbl1.ID
, CASE WHEN tbl2.Color IS NULL THEN ''  ELSE tbl2.Color END AS 'Batch-Color'
, tbl2.DateRegister as 'Date-1'
, CASE WHEN tbl2.DateRegister IS NULL  THEN '' ELSE WHEN tbl2.DateRegister END AS 'Batch-Date'
FROM tbl1 LEFT JOIN tbl2 ON tbl1.ID = tbl2.ID

Here is the output. I need the date to display as a blank string instead of '1900-01-01'

ID    Batch-Color    Date-1                      Batch-Date
1     'Red'          '2021-05-28 00:00:00.000'   '2021-05-28 00:00:00.000'
2     ''             NULL                        '1900-01-01 00:00:00.000'
3     'Blue'         '2021-02-04 00:00:00.000'   '2021-02-04 00:00:00.000'

The best approach is to simply keep the NULL values, and let your client code format it as what it wants. If you convert to blanks, you also have to decide what format to use to convert the dates that are not NULL -- relying on what the server implicitly produces based on regional settings is a bad idea. — Jeroen Mostert
– Jeroen Mostert, Commented May 29, 2021 at 8:38
Can't agree more with the above comment. '' is by definition not a date and you can't store or resolve non-date values in a date and time column in SQL; a column must be a single strongly typed data type. Leave it to your presentation layer to display NULL as a blank value, not the RDBMS. — Thom A
– Thom A ♦, Commented May 29, 2021 at 8:56

forpas · Accepted Answer · 2021-05-29 08:40:52Z

2

You must convert DateRegister to VARCHAR with style = 121 (to preserve the format that you want).
Also, instead of the CASE expressions, it's simpler with COALESCE():

SELECT tbl1.ID, 
       COALESCE(tbl2.Color, '') AS [Batch-Color],
       tbl2.DateRegister AS [Date-1],
       COALESCE(CONVERT(VARCHAR(23), tbl2.DateRegister, 121), '') AS [Batch-Date]
FROM tbl1 LEFT JOIN tbl2 
ON tbl1.ID = tbl2.ID

See the demo.

edited May 29, 2021 at 8:40

answered May 29, 2021 at 8:39

forpas

165k10 gold badges51 silver badges85 bronze badges

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

3 Comments

Jeroen Mostert Over a year ago

Using VARCHAR without a length is a bad habit to kick.

forpas Over a year ago

@JeroenMostert yes, I had to count, it's 23 max.

Jaime Dolor jr. Over a year ago

This worked for me. I got the results I needed. Much obliged. :-)

Stu · Accepted Answer · 2021-05-29 08:37:31Z

0

You need to cast your date to a varchar, otherwise the blank string is being implicitely cast to date hence your 1900-01-01.

Try this pattern:

select... coalesce(convert(varchar(25),[Batch-Date]),'')...

answered May 29, 2021 at 8:37

Stu

32.7k6 gold badges17 silver badges34 bronze badges

Collectives™ on Stack Overflow

How do I code NULL Dates as blank in MS SQL

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related