SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 NULL dates and removing NULL fields
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 11/23/2012 :  03:42:49  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 11/23/2012 :  03:50:19  Show Profile  Reply with Quote
COALESCE( fieldName, '') -----> It will replace your NULL values with empty string


--
Chandu
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 11/23/2012 :  04:13:36  Show Profile  Reply with Quote
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.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 11/23/2012 :  04:26:09  Show Profile  Reply with Quote
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
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 11/23/2012 :  04:37:50  Show Profile  Reply with Quote
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)
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 11/23/2012 :  04:47:28  Show Profile  Reply with Quote
Use this one.......

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

--
Chandu
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 11/29/2012 :  05:17:32  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 11/29/2012 :  05:36:37  Show Profile  Reply with Quote
Thanks for the info folks.
I've just checked another table and it appears that NULL values are acceptable in this particular field.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000