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.

> **Solution Hi someone answered this to do as.
>      ISNULL(CONVERT(varchar(50),  [Amort Into Default] ,120),'') and it works  I dont know why .

**

In the result set all values of one field have to be the same data type. So you can't output some values as VARCHAR and other as a DATETIME. You should parse it on client side or output all values as a VARCHAR type. — valex
– valex, Commented Nov 12, 2013 at 13:39
A datetime value is incapable of storing a string. There is no way to simultaneously have a) that the type of the result expression is of type datetime, and b) that a row contains something other than a valid datetime value or NULL. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Nov 12, 2013 at 13:49

Ben · Accepted Answer · 2013-11-12 14:07:30Z

50

CASE and CAST should work:

CASE WHEN mycol IS NULL THEN '' ELSE CONVERT(varchar(50), mycol, 121) END

edited Nov 12, 2013 at 14:07

Ben

35.8k6 gold badges81 silver badges117 bronze badges

answered Nov 12, 2013 at 13:50

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

GPH · Accepted Answer · 2013-11-12 14:55:08Z

14

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]

answered Nov 12, 2013 at 14:55

GPH

1,9073 gold badges29 silver badges50 bronze badges

4 Comments

natur3 Over a year ago

this worked really good for me. I had some case statements working out the dates I wanted and just kept the 1900-01-01 date in there into a temp table then just applied this line to each applicable line from the select on the temp table. I only did this because I'm working with only 13 columns; anymore than that I would have done something else - it was just cleaner and more readable.

Jaime Over a year ago

why invoking CONVERT twice? You need only evaluate IS NULL, like the answer of @Bohemian

Libertine Makinta Over a year ago

Worked great. Just had to be mindful of the date format.

HappyCoder123 Over a year ago

would be able to use the same logic in an SSIS expression? if so how would it be formulated

shadow · Accepted Answer · 2015-06-12 10:29:16Z

7

declare @date datetime; set @date = null
--declare @date datetime; set @date = '2015-01-01'

select coalesce( convert( varchar(10), @date, 103 ), '')

answered Jun 12, 2015 at 10:29

shadow

1,9031 gold badge17 silver badges25 bronze badges

1 Comment

Eli Over a year ago

I did something similar to you, though used ISNULL in lieu of COALESCE

Eli · Accepted Answer · 2017-07-26 16:04:16Z

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]

Ryan Pratt · Accepted Answer · 2015-11-24 22:36:15Z

2

This also works:

REPLACE(ISNULL(CONVERT(DATE, @date), ''), '1900-01-01', '') AS 'Your Date Field'

answered Nov 24, 2015 at 22:36

Ryan Pratt

211 bronze badge

1 Comment

Olivier De Meulder Over a year ago

Welcome to Stack Overlow. In the future, consider adding a little more explanation.

JackLock · Accepted Answer · 2019-09-18 03:56:59Z

2

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.

edited Sep 18, 2019 at 3:56

JackLock

1,1681 gold badge13 silver badges27 bronze badges

answered Sep 17, 2019 at 18:21

karthick sridhar

212 bronze badges

Comments

Si8 · Accepted Answer · 2017-04-04 13:35:33Z

1

select case when IsNull(CONVERT(DATE, StartDate),'')='' then 'NA' else Convert(varchar(10),StartDate,121) end from table1

edited Apr 4, 2017 at 13:35

Si8

9,25522 gold badges120 silver badges223 bronze badges

answered Apr 4, 2017 at 13:01

Jothi Chitrambalam

111 bronze badge

1 Comment

Nander Speerstra Over a year ago

Please add an explanation to you code (and you can indent it with four spaces to create a code block).

Abhishek Gurjar · Accepted Answer · 2017-04-14 07:36:47Z

1

Select isnull(date_column_name,cast('1900-01-01' as DATE)) from table name

edited Apr 14, 2017 at 7:36

Abhishek Gurjar

7,47610 gold badges41 silver badges47 bronze badges

answered Apr 14, 2017 at 7:22

mohd imran khan

111 bronze badge

Comments

ODU90DBA · Accepted Answer · 2013-11-12 15:05:41Z

0

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!

answered Nov 12, 2013 at 15:05

ODU90DBA

864 bronze badges

Comments

Guest · Accepted Answer · 2019-03-22 15:11:09Z

0

Try to use the function DECODE

Ex: Decode(MYDATE, NULL, ' ', MYDATE)

If date is NULL then display ' ' (BLANK) else display the date.

answered Mar 22, 2019 at 15:11

Guest

1

Collectives™ on Stack Overflow

Change NULL values in Datetime format to empty string

10 Answers 10

Comments

4 Comments

1 Comment

Comments

1 Comment

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

10 Answers 10

Comments

4 Comments

1 Comment

Comments

1 Comment

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related