I have a table which contains 'NULL' values which are of type 'Datetime'. Now i have to convert those into empty string but when when i use convert function
ISNULL( [Accrued Out of Default] ,'' )
here accrued into default is of datetime type, what it does it changes null to '1900-01-01 00:00:00.000' instead of empty
Then i try to convert them into varchar and apply same
ISNULL(CONVERT(varchar(50), [Amort Into Default] ),'')
Now I am able to convert into empty string but now those datetime are converted to string which I needed in datetime So I try to CAST, CONVERT but non of them works.
CONVERT(Datetime,'ISNULL(CONVERT(varchar(50), [Amort Into Default] ),'')',120)
This gives error.
Is there any possible solution to this.
> **Solution Hi someone answered this to do as.
> ISNULL(CONVERT(varchar(50), [Amort Into Default] ,120),'') and it works I dont know why .
**
This question is related to
sql-server
sql-server-2008
datetime
This also works:
REPLACE(ISNULL(CONVERT(DATE, @date), ''), '1900-01-01', '') AS 'Your Date Field'
I had something similar, and here's (an edited) version of what I ended up using successfully:
ISNULL(CONVERT(VARCHAR(50),[column name goes here],[date style goes here] ),'')
Here's why this works: If you select a date which is NULL, it will show return NULL, though it is really stored as 01/01/1900. This is why an ISNULL on the date field, while you're working with any date data type will not treat this as a NULL, as it is technically not being stored as a NULL.
However, once you convert it to a new datatype, it will convert it as a NULL, and at that point, you're ISNULL will work as you expect it to work.
I hope this works out for you as well!
~Eli
Update, nearly one year later:
I had a similar situation, where I needed the output to be of the date data-type, and my aforementioned solution didn't work (it only works if you need it displayed as a date, not be of the date data type.
If you need it to be of the date data-type, there is a way around it, and this is to nest a REPLACE
within an ISNULL
, the following worked for me:
Select
ISNULL(
REPLACE(
[DATE COLUMN NAME],
'1900-01-01',
''
),
'') AS [MeaningfulAlias]
using an ISNULL is the best way I found of getting round the NULL in dates :
ISNULL(CASE WHEN CONVERT(DATE, YOURDate) = '1900-01-01' THEN '' ELSE CONVERT(CHAR(10), YOURDate, 103) END, '') AS [YOUR Date]
declare @mydatetime datetime
set @mydatetime = GETDATE() -- comment out for null value
--set @mydatetime = GETDATE()
select
case when @mydatetime IS NULL THEN ''
else convert(varchar(20),@mydatetime,120)
end as converted_date
In this query, I worked out the result came from current date of the day.
declare @date datetime; set @date = null
--declare @date datetime; set @date = '2015-01-01'
select coalesce( convert( varchar(10), @date, 103 ), '')
Try to use the function DECODE
Ex: Decode(MYDATE, NULL, ' ', MYDATE)
If date is NULL then display ' ' (BLANK) else display the date.
select case when IsNull(CONVERT(DATE, StartDate),'')='' then 'NA' else Convert(varchar(10),StartDate,121) end from table1
CASE and CAST should work:
CASE WHEN mycol IS NULL THEN '' ELSE CONVERT(varchar(50), mycol, 121) END
Select isnull(date_column_name,cast('1900-01-01' as DATE)) from table name
You could try the following
select case when mydatetime IS NULL THEN '' else convert(varchar(20),@mydatetime,120) end as converted_date from sometable
-- Testing it out could do --
declare @mydatetime datetime
set @mydatetime = GETDATE() -- comment out for null value
--set @mydatetime = GETDATE()
select
case when @mydatetime IS NULL THEN ''
else convert(varchar(20),@mydatetime,120)
end as converted_date
Hope this helps!
Source: Stackoverflow.com