CODEWITHSUNDEEP
sql-server
Jaime Dolor jr.
Jaime Dolor jr.

Reputation: 919

How do I code NULL Dates as blank in MS SQL

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'

Upvotes: 1

Views: 2309

Answers (2)

forpas
forpas

Reputation: 164064

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.

Upvotes: 2

Stu
Stu

Reputation: 32579

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]),'')...

Upvotes: 0

Related Questions