Change NULL values in Datetime format to empty string

Question

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    gt    Solution Hi someone answered this to do as   gt       ISNULL CONVERT varchar 50     Amort Into Default   120      and it works  I dont know why

User · Answer

Try to use the function DECODE  Ex  Decode MYDATE  NULL       MYDATE   If date is NULL then display      BLANK  else display the date

User · Answer

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

User · Answer

Select isnull date column name cast  1900-01-01  as DATE   from table name

User · Answer

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

User · Answer

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

User · Answer

CASE and CAST should work   CASE WHEN mycol IS NULL THEN    ELSE CONVERT varchar 50   mycol  121  END

User · Answer

declare  date datetime  set  date   null --declare  date datetime  set  date    2015-01-01   select coalesce  convert  varchar 10    date  103

User · Answer

select case when IsNull CONVERT DATE  StartDate         then  NA  else Convert varchar 10  StartDate 121  end from table1

User · Answer

This also works   REPLACE ISNULL CONVERT DATE   date         1900-01-01       AS  Your Date Field

User · Answer

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

[sql-server] Change NULL values in Datetime format to empty string

Examples related to sql-server

Examples related to sql-server-2008

Examples related to datetime