TBSupport
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I edited my above comment to remove the '' comment, as '' is not a valid date value.
Give this a whirl..
Give this a whirl..
SELECT CASE WHEN CAST(DOCDATE as date) <> '19000101' THEN CAST(DOCDATE as date) ELSE NULL END as DOCDATE
FROM DOCDATE
btw Eyeball this part, as GETDATE() does not take any arguments --> GETDATE(ME97705.DOCDATE)
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.DOCD ATE, '') as DATE) AS [DocumentDate]
Thanks!
TBSupport
Disregard. I figured it out:
cast(COALESCE(ME97705.DOCD
Thanks!
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
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_
END AS [CloseDate] from ME97708.ME_Job_Close_Date,
How do I fix this?
Thanks!
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
CASE WHEN cast(ME97708.ME_Job_Close_
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
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),datetimecolum n,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.
see https://www.experts-exchange.com/articles/12315/SQL-Server-Date-Styles-formats-using-CONVERT.html
e.g. convert (varchar(10),datetimecolum
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.
Apologies for typos. (above) will correct when I have a real keyboard.
+ edit: Now done.
+ edit: Now done.
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.
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.
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
Whoops! Actually, I revised it to the following:
CASE WHEN cast(ME97708.ME_Job_Close_
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
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
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(Var Char(10),C AST('1900- 01-01' as date),101),''),'1900-01-01','') as todays_date
result
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()
e.g.
if the style number 101 is used (MM-DD-YYYY) then
REPLACE(convert(varchar(10
is the equivalent of this:
replace('01/01/1900','1900
and the replacement is not performed.
try this:
SELECT Replace(IsNull(Convert(Var
result
| todays_date |
|-------------|
| 01/01/1900 |
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()
ASKER
I tried the case formula but could not get it to work:
COALESCE(CAST(GETDATE(ME97
Can you please help?
Thanks!
TBSupport