T-SQL: Stripping Out Time from DateTime and Making 01/01/1900 Be Blank

Hello:

Below is my T-SQL query.  You will notice two datetime fields as [DocumentDate} and [CloseDate].

How do I strip out the time (i.e. the midnight element) and make both fields simply be dates, and how do I make 01/01/1900 be blank instead of a date/datetime?

Thanks!

TBSupport

SELECT DISTINCT 
                      ME97708.ME_Job_ID AS [Project], ME97708.ME_User_Defined AS [Editor], COALESCE (ME97705.DOCDATE, '') 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 ME97708.ME_Job_Close_Date <> '1900-01-01 00:00:00.000' 
                      THEN ME97708.ME_Job_Close_Date ELSE NULL 
                      END AS [CloseDate], CASE WHEN ME97708.ME_Job_Close_Date = '1900-01-01 00:00:00.000' 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 INNER JOIN
                      ME97702 ON ME97708.CUSTNMBR = ME97702.MEuserdefined3
/*where ME97708.CUSTNMBR = 'BCFB'*/ UNION ALL
SELECT DISTINCT 
                      ME97707.ME_Job_ID AS [Project], ME97707.ME_User_Defined AS [Editor], COALESCE (ME97704.DOCDATE, '') 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 ME97707.ME_Job_Close_Date <> '1900-01-01 00:00:00.000' THEN ME97707.ME_Job_Close_Date ELSE NULL 
                      END AS [Close Date], CASE WHEN ME97707.ME_Job_Close_Date = '1900-01-01 00:00:00.000' 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 INNER JOIN
                      ME97702 ON ME97707.CUSTNMBR = ME97702.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.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>How do I strip out the time (i.e. the midnight element) and make both fields simply be dates
You can always CAST a datetime column as a date to strip off the time
SELECT CAST(GETDATE() as date) as todays_date

Open in new window

>and how do I make 01/01/1900 be blank instead of a date/datetime?
Blank is not an acceptable value for a date/datetime column, so I'd change it to NULL, and let whatever front-end you're using display it as a blank
SELECT CASE WHEN datetime_column = '19000101' THEN NULL ELSE datetime_column END as datetime_column

Open in new window

Wouldn't be a bad idea to look at your applications/whatever populates this table and see if you can avoid them populating 'empty' dates as 1900-01-01.
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
TBSupportAuthor Commented:
Hi Jim:

I tried the case formula but could not get it to work:

COALESCE(CAST(GETDATE(ME97705.DOCDATE) as date), '') as [DocumentDate],

Can you please help?

Thanks!

TBSupport
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I edited my above comment to remove the '' comment, as '' is not a valid date value.

Give this a whirl..
SELECT CASE WHEN CAST(DOCDATE as date) <> '19000101' THEN CAST(DOCDATE as date) ELSE NULL END as DOCDATE
FROM DOCDATE

Open in new window

btw Eyeball this part, as GETDATE() does not take any arguments --> GETDATE(ME97705.DOCDATE)
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.

TBSupportAuthor Commented:
Shoot!  I meant to say that I could not get the "cast" (not "case") formula to work.  Can you please show me how, Jim?

TBSupport
0
TBSupportAuthor Commented:
Also, you had mentioned "to let whatever front-end you're using display it as a blank".  Crystal is not letting me do so.  Do you have any workaround?

TBSupport
0
TBSupportAuthor Commented:
Hi Jim:

Disregard.  I figured it out:

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

Thanks!

TBSupport
0
TBSupportAuthor Commented:
Hi JIm:

I don't think that I figured you case statement out.  The closest that I could come was the statement below, and I got a syntax error:

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] from ME97708.ME_Job_Close_Date,

How do I fix this?

Thanks!

TBSupport
0
TBSupportAuthor Commented:
Whoops!  Actually, I revised it to the following:

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]

I did not get a syntax error.  But, every record gave me a NULL value.  That's incorrect.  Not all of the dates are NULL.

TBSupport
0
PortletPaulfreelancerCommented:
Relax. the first thing to recognize here is that datetime, date and a blank string are 3 different data types. while date and datetime are compatible, nether are compatible with blank string. so to get these data types into a single column you need  to also convert the dates to a string.

see http://www.experts-exchange.com/articles/12315/SQL-Server-Date-Styles-formats-using-CONVERT.html

e.g. convert (varchar(10),datetimecolumn,121)

this strips of time as well. choose a style number that provides the wanted output. (e.g. 121 is the style number I chose)

So I would try something like this:

case when datecol = '19000101' then '' else convert (varchar(10),datecol,121) end

{+edits}
please note it is the 10 character length of the varchar(10) that truncates to show just the date.
0
PortletPaulfreelancerCommented:
Apologies for typos. (above) will correct when I have a real keyboard.

+ edit: Now done.
0
PortletPaulfreelancerCommented:
just an observation, but your current query structure is:

select distinct ...
union all
select distinct ...

have you tried the following structure instead?

select  ...
union
select ...

it may be faster, and you end up with only a unique set of row.
0
TBSupportAuthor Commented:
Actually, I was wrong, when I stated the following:

Whoops!  Actually, I revised it to the following:

 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]

 I did not get a syntax error.  But, every record gave me a NULL value.  That's incorrect.  Not all of the dates are NULL.


I found that one "part" of my programming did require completely NULL values for the CloseDate field.  So, Jim Horn was correct!

TBSupport
0
Andrei FomitchevCommented:
SELECT IsNull(Convert(VarChar,CAST(NULL as date),121),'') as todays_date
-- returns BLANK

SELECT Replace(IsNull(Convert(VarChar(10),CAST('1900-01-01' as date),121),''),'1900-01-01','') as todays_date

-- returns BLANK as well

Open in new window

0
PortletPaulfreelancerCommented:
The string function REPLACE(), conducted after conversion to varchar, relies on the style number and can be easily broken. I would not recommend that approach

e.g.
if the style number 101 is used (MM-DD-YYYY) then
REPLACE(convert(varchar(10), datecolumn , 101), '1900-01-01','')

is the equivalent of this:
       replace('01/01/1900','1900-01-01','')

       and the replacement is not performed.

try this:

SELECT Replace(IsNull(Convert(VarChar(10),CAST('1900-01-01' as date),101),''),'1900-01-01','') as todays_date

result
| todays_date |
|-------------|
|  01/01/1900 |

Open in new window



This approach does NOT rely on a style number & it covers both NULL or 1900-01-01:

case when datecol = '19000101'  or datecol IS NULL
        then ''
        else convert (varchar(10),datecol,121)  --<< only one reference to style to maintain
end

and there is only one function call too.

see: SQL Server Date Styles (formats) using CONVERT()
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.