NULL dates and removing NULL fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

NULL dates and removing NULL fields

Author

Topic

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-11-23 : 03:42:49

Hi all

I've a fairly lengthy query that pulls back various bits of information.
One of them is a date field which may not be completed.
How can I show this as an empty field?

I've setting it to NULL but SQL puts in "01/01/1900" instead.
I need to keep this in date format which probably doesn't help.

Also, some of the other fields contain NULL values.
Is there a quick way of removing all NULLs and replacing them with empty strings or do I have to do them a field at a time?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-23 : 03:50:19

COALESCE( fieldName, '') -----> It will replace your NULL values with empty string

--
Chandu

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-11-23 : 04:13:36

I've tried that but I wondered if it was possible to do multiple fields at a time?
Also, when I do that for dates (I'm using the ISNULL function), it puts 01/01/1900 in the date field.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-23 : 04:26:09

See this example..
If u use coalesce function for DATE field with '' (empty string), then it fills that column with 1900-01-01

create table temp
(
dateField date,
category varchar(3),
amount money
)
GO
insert into temp values ('1/1/2012', 'ABC', 1000.00)
insert into temp values ('2/1/2012', NULL, NULL)
insert into temp values ('2/1/2012', 'GHI', 800.00)
insert into temp values ('4/10/2012', 'DEF', NULL)
insert into temp values (NULL, 'DEF', NULL)
insert into temp values ('4/1/2012', 'ABC', 300)
GO

SELECT coalesce(datefield, NULL), coalesce(category, ''), coalesce(amount, 0) FROM temp
SELECT coalesce(datefield, ''), coalesce(category, ''), coalesce(amount, 0) FROM temp
GO
DROP TABLE temp

--
Chandu

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-11-23 : 04:37:50

It's the 1900-01-01 I'm trying to get rid of.
I want to get the date field to be empty (i.e. not 1900-01-01 and not NULL)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-23 : 04:47:28

Use this one.......

coalesce(cast(datefield AS varchar), '')

--
Chandu

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-11-29 : 05:17:32

Note that it is not possible to put emptry string in a datetime column http://beyondrelational.com/modules/2/blogs/70/posts/10841/empty-string-and-default-values.aspx

Madhivanan

Failing to plan is Planning to fail