T-SQL: Making a 01/01/1900 or NULL Date Field Be Blank

I'm seeing a weird COALESCE call

cast(COALESCE (ME97704.DOCDATE, '') AS DATE) AS [DocumentDate]

If DOCDATE and ME_Job_Close_Date are indeed DATE types then coalescing them to '' wouldn't make much sense. If you want to display a blank you'd need to cast them to something like a varchar field.

see related question here

I believe from the earlier question that you are attempting to remove the time portion from datetime columns using CAST([column] as date) and, in this question, ensure you get blanks

As I tried to explain in that question (unsuccessfully) you do not need to use CAST(... as DATE) for trimming off time in this query.

You can achieve BOTH trimming off the time portion and providing "blank" if you handle the columns as varchar.

Here is a  tiny sample of data, it shows dates with times, dates at 1900-01-01 and dates that are NULL

CREATE TABLE ME97708
    (id int, [ME_Job_Close_Date] datetime, [Docdate] datetime)
;
    
INSERT INTO ME97708
    ([id],[ME_Job_Close_Date], [Docdate])
VALUES
    (1,'2015-08-01 13:14:15', '2015-07-01 13:14:15'),
    (2,'1900-01-01', '1900-01-01'),
    (3,NULL, NULL),
    (4,'2015-08-02 13:14:15', '2015-07-01 13:14:15')
;

CREATE TABLE ME97707
    (id int, [ME_Job_Close_Date] datetime, [Docdate] datetime)
;
    
INSERT INTO ME97707
    ([id],[ME_Job_Close_Date], [Docdate])
VALUES
    (1,'2015-08-01 13:14:15', '2015-07-01 13:14:15'),
    (2,'1900-01-01', '1900-01-01'),
    (3,NULL, NULL),
    (4,'2015-08-02 13:14:15', '2015-07-01 13:14:15')
;

Select allOpen in new window

Using this query:

SELECT
      id
    , CASE
            WHEN ISNULL([Docdate], '19000101') = '19000101' THEN ''
            ELSE CONVERT(varchar(10), [Docdate], 121)
      END AS [DocumentDate]
    , CASE
            WHEN ISNULL([ME_Job_Close_Date], '19000101') = '19000101' THEN ''
            ELSE CONVERT(varchar(10), [ME_Job_Close_Date], 121)
      END AS [CloseDate]
FROM ME97708

UNION ALL

SELECT
      id
    , CASE
            WHEN ISNULL([Docdate], '19000101') = '19000101' THEN ''
            ELSE CONVERT(varchar(10), [Docdate], 121)
      END AS [DocumentDate]
    , CASE
            WHEN ISNULL([ME_Job_Close_Date], '19000101') = '19000101' THEN ''
            ELSE CONVERT(varchar(10), [ME_Job_Close_Date], 121)
      END AS [CloseDate]
FROM ME97707

Select allOpen in new window

It produces this result, with "blanks" instead of NULLs or 1900-01-01:

| id | DocumentDate |  CloseDate |
|----|--------------|------------|
|  1 |   2015-07-01 | 2015-08-01 |
|  2 |              |            |
|  3 |              |            |
|  4 |   2015-07-01 | 2015-08-02 |
|  1 |   2015-07-01 | 2015-08-01 |
|  2 |              |            |
|  3 |              |            |
|  4 |   2015-07-01 | 2015-08-02 |
        

Select allOpen in new window

You can see this operating at: http://sqlfiddle.com/#!3/980c8/1

-------
notes

the 121 in  CONVERT(varchar(10), [Docdate], 121) is a style number. That style number determines to output you get, for different styles
see https://www.experts-exchange.com/articles/12315/SQL-Server-Date-Styles-formats-using-CONVERT.html
e.g. if you prefer MM-DD-YYYY then use style number 110

the varchar(10) in CONVERT(varchar(10), [Docdate], 121 is how you trim off the time, by limiting the output to just the YYYY-MM-DD portion

'19000101' in that EXACT literal is deliberate, and using YYYYMMDD format as date literals is the safest possible format in SQL Server

{+edit}
Above I use a shorthand method for handling NULLS that perhaps will only confuse. So below is what I would actually do for handling of NULLs myself (use  OR [column] IS NULL):

SELECT
      id
    , CASE
            WHEN [Docdate] = '19000101' or [Docdate] IS NULL THEN ''
            ELSE CONVERT(varchar(10), [Docdate], 121)
      END AS [DocumentDate]
    , CASE
            WHEN [ME_Job_Close_Date] = '19000101' or [ME_Job_Close_Date] IS NULL THEN ''
            ELSE CONVERT(varchar(10), [ME_Job_Close_Date], 121)
      END AS [CloseDate]
FROM ME97708

UNION ALL

SELECT
      id
    , CASE
            WHEN [Docdate] = '19000101' or [Docdate] IS NULL THEN ''
            ELSE CONVERT(varchar(10), [Docdate], 121)
      END AS [DocumentDate]
    , CASE
            WHEN [ME_Job_Close_Date] = '19000101' or [ME_Job_Close_Date] IS NULL THEN ''
            ELSE CONVERT(varchar(10), [ME_Job_Close_Date], 121)
      END AS [CloseDate]
FROM ME97707
;

Select allOpen in new window

Step-by-step explanation - 1,2,3. 3 is the solution,

--

--
-- 1.Convert Date to VarChar
--
SELECT id, ME_Job_Close_Date, Convert(Varchar,Docdate,121) AS Docdate FROM ME97708
UNION ALL
SELECT id, ME_Job_Close_Date, Docdate FROM ME97707
/*
id	ME_Job_Close_Date	        Docdate
1	2015-08-01 13:14:15.000	2015-07-01 13:14:15.000
2	1900-01-01 00:00:00.000	1900-01-01 00:00:00.000
3	NULL	                                NULL
4	2015-08-02 13:14:15.000	2015-07-01 13:14:15.000
1	2015-08-01 13:14:15.000	2015-07-01 13:14:15.000
2	1900-01-01 00:00:00.000	1900-01-01 00:00:00.000
3	NULL	                                NULL
4	2015-08-02 13:14:15.000	2015-07-01 13:14:15.000
*/
--
-- 2. Replace NULL with 1900-01-01 00:00:00.000 everywhere
--
SELECT id, IsNull(ME_Job_Close_Date,'') AS ME_Job_Close_Date, 
	IsNull(Docdate,'') AS Docdate
	FROM (
	SELECT id, ME_Job_Close_Date, Convert(Varchar,Docdate,121) AS Docdate FROM ME97708
	UNION ALL
	SELECT id, ME_Job_Close_Date, Docdate FROM ME97707
) t
/*
id	ME_Job_Close_Date	        Docdate
1	2015-08-01 13:14:15.000	2015-07-01 13:14:15.000
2	1900-01-01 00:00:00.000	1900-01-01 00:00:00.000
3	1900-01-01 00:00:00.000	1900-01-01 00:00:00.000
4	2015-08-02 13:14:15.000	2015-07-01 13:14:15.000
1	2015-08-01 13:14:15.000	2015-07-01 13:14:15.000
2	1900-01-01 00:00:00.000	1900-01-01 00:00:00.000
3	1900-01-01 00:00:00.000	1900-01-01 00:00:00.000
4	2015-08-02 13:14:15.000	2015-07-01 13:14:15.000
*/
--
-- 3. Replace 1900-01-01 00:00:00.000 with ''
--
SELECT id, 
	Replace(IsNull(ME_Job_Close_Date,''),'1900-01-01 00:00:00.000','') AS ME_Job_Close_Date, 
	Replace(IsNull(Docdate,''),'1900-01-01 00:00:00.000','') AS Docdate
FROM(
	SELECT id,
	Convert(Varchar,ME_Job_Close_Date,121) AS ME_Job_Close_Date, 
	Convert(Varchar,Docdate,121) AS Docdate 
	FROM ME97708
	UNION ALL
	SELECT id, 
	Convert(Varchar,ME_Job_Close_Date,121) AS ME_Job_Close_Date, 
	Convert(Varchar,Docdate,121) 
	FROM ME97707
) t
/*
id	ME_Job_Close_Date	        Docdate
1	2015-08-01 13:14:15.000	2015-07-01 13:14:15.000
2		
3		
4	2015-08-02 13:14:15.000	2015-07-01 13:14:15.000
1	2015-08-01 13:14:15.000	2015-07-01 13:14:15.000
2		
3		
4	2015-08-02 13:14:15.000	2015-07-01 13:14:15.000
*/

Select allOpen in new window

"1,2,3 is a solution" : using the test data I provided, those steps arrive at the same final result as my suggestion, so there is more than one approach.

Step 2 does not "replace" null with 1900-01-01 because a date column cannot hold a blank string.

    SELECT id, IsNull(ME_Job_Close_Date,'') AS ME_Job_Close_Date,  IsNull(Docdate,'') AS Docdate

that use of ISNULL() forces supply of a default date which happens to be 1st Jan 1900.


For the sake of "self documenting code" it would be clearer (IMO) to specify the date wanted.

SELECT id, IsNull(ME_Job_Close_Date,'19000101') AS ME_Job_Close_Date,
      IsNull(Docdate,'19000101') AS Docdate
      FROM (
      SELECT id, ME_Job_Close_Date, Docdate AS Docdate FROM ME97708 --nb removed convert(..)
      UNION ALL
      SELECT id, ME_Job_Close_Date, Docdate FROM ME97707
) t

nb: YYYYMMDD is a "safe format"

... the only truly safe formats for date/time literals in SQL Server, at least for DATETIME and SMALLDATETIME, are:

YYYYMMDD
YYYY-MM-DDThh:mm:ss[.nnn]

Aaron Bertrand

I still prefer using the simple case expressions I proposed:

SELECT
      id
    , CASE
            WHEN [Docdate] = '19000101' or [Docdate] IS NULL THEN ''
            ELSE CONVERT(varchar(10), [Docdate], 121)
      END AS [DocumentDate]
    , CASE
            WHEN [ME_Job_Close_Date] = '19000101' or [ME_Job_Close_Date] IS NULL THEN ''
            ELSE CONVERT(varchar(10), [ME_Job_Close_Date], 121)
      END AS [CloseDate]
FROM ME97708

UNION ALL

SELECT
      id
    , CASE
            WHEN [Docdate] = '19000101' or [Docdate] IS NULL THEN ''
            ELSE CONVERT(varchar(10), [Docdate], 121)
      END AS [DocumentDate]
    , CASE
            WHEN [ME_Job_Close_Date] = '19000101' or [ME_Job_Close_Date] IS NULL THEN ''
            ELSE CONVERT(varchar(10), [ME_Job_Close_Date], 121)
      END AS [CloseDate]
FROM ME97707
;

Select allOpen in new window

Remember to change the style number to the one that is relevant to you, for that see: SQL Server Date Styles (formats) using CONVERT()

Isn't this just effectively a re-post of the following thread here?

A number of us spent several days trying to help this same user with a very similar problem.  There was a need for more details on exactly what the user was asking for and there was a basic lack of knowledge about the proper use of Date and DateTime columns.  I'm all for helping folks out but at some point the user has to recognize they need more education on a topic.

I feel for the OP as they have obviously been put in charge of a system for which they don't have the needed training to do well.

It also seems the OP has a system that has been implemented poorly, where a date of 1/1/1900 is a special case where the "case" is still "open".  To me, this system should have used a null to indicate an "open" status.  Also, as stated above, you can't have a column be a Date or DateTime and return a "blank" value.  Perhaps reading a book on SQL and Crystal Reports would be helpful.

My condolences to the OP but I will have to drop out of this discussion.  I've spent too much time on it already.  Perhaps some of you can provide the time needed to help educate the OP on this.

TBSupport, do you still need help with this question?

No.  I will close this, soon.

TBSupport