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

Hello:

Below is my T-SQL view.

When DocumentDate is blank, it is 01-01-1900.  When CloseDate is blank, it is NULL.

How can I make these two fields be blank for either 01-01-1900 or NULL, yet, still maintain the fields as date fields?

Thanks!

TBSupport

SELECT --DISTINCT 
                      ME97708.ME_Job_ID AS [Project], ME97708.ME_User_Defined AS [Editor], cast(COALESCE (ME97705.DOCDATE, '') AS DATE) AS [DocumentDate], ME97708.ME_Work_Scope AS [Product], 
                      ME97702.DSCRIPTN AS [Company], CASE WHEN ME97705.ME_Breakdown_Code = 'SALES' THEN ME97705.TRXAMNT ELSE 0 END AS [Sales], COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], 
                      CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97705.TRXAMNT ELSE 0 END AS [TransAmt], 
                      /*COALESCE(ME97708.ME_Job_Close_Date, '') AS [Close Date],*/ CASE WHEN cast(ME97708.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97708.ME_Job_Close_Date AS DATE) 
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97708.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97708 LEFT OUTER JOIN
                      ME97705 ON ME97705.ME_Job_ID = ME97708.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97705.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97708.MEuserdefined3
/*where ME97708.CUSTNMBR = 'BCFB'*/ UNION ALL
SELECT --DISTINCT 
                      ME97707.ME_Job_ID AS [Project], ME97707.ME_User_Defined AS [Editor], cast(COALESCE (ME97704.DOCDATE, '') AS DATE) AS [DocumentDate], ME97707.ME_Work_Scope AS [Product], 
                      ME97702.DSCRIPTN AS [Company], CASE WHEN ME97704.ME_Breakdown_Code = 'SALES' THEN ME97704.TRXAMNT ELSE 0 END AS [Sales], COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], 
                      CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97704.TRXAMNT ELSE 0 END AS [TransAmt], 
                      /*COALESCE(ME97707.ME_Job_Close_Date, '') As [CloseDate],*/ CASE WHEN cast(ME97707.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97707.ME_Job_Close_Date AS DATE) 
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97707.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97707 LEFT OUTER JOIN
                      ME97704 ON ME97704.ME_Job_ID = ME97707.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97704.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97707.MEuserdefined3

Open in new window

LVL 1
TBSupportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russ SuterCommented:
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.
0
pcelbaCommented:
T-SQL does not know blank date field like e.g. FoxPro. You either have date defined or NULL. It is also not possible to mix two different data types in one column like in e.g. SQLite.

The work around is to populate two fields in your view. The first one is of date/datetime data type which is suitable for calculations, the second one can be of the character data type suitable for display purposes.

You may also solve this task at the client side where you can decide how to display NULL dates.
You may also decide to display the date 1900-01-01 as a blank field.

And as I see you know how to use the CASE expressions to achieve all these conversions.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
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')
;

Open 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

Open 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 |
        

Open 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 http://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
;

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Andrei FomitchevCommented:
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
*/

Open in new window

0
PortletPaulfreelancerCommented:
"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
;

Open 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()
0
larryhSr. Software EngineerCommented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
TBSupport, do you still need help with this question?
0
TBSupportAuthor Commented:
No.  I will close this, soon.

TBSupport
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.