asked on

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

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TBSupport

ASKER

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

Jim Horn

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)

TBSupport

ASKER

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

TBSupport

ASKER

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

TBSupport

ASKER

Hi Jim:

Disregard. I figured it out:

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

Thanks!

TBSupport

TBSupport

ASKER

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

TBSupport

ASKER

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

PortletPaul

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 https://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.

PortletPaul

Apologies for typos. (above) will correct when I have a real keyboard.

+ edit: Now done.

PortletPaul

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.

TBSupport

ASKER

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

Andrei Fomitchev

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

PortletPaul

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()