| Author |
 Topic  |
|
|
rmg1
Posting Yak Master
245 Posts |
 Posted - 11/23/2012 : 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
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 11/23/2012 : 03:50:19
|
COALESCE( fieldName, '') -----> It will replace your NULL values with empty string
--
Chandu |
 |
|
|
rmg1
Posting Yak Master
245 Posts |
Posted - 11/23/2012 : 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
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 11/23/2012 : 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
Posting Yak Master
245 Posts |
Posted - 11/23/2012 : 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
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 11/23/2012 : 04:47:28
|
Use this one.......
coalesce(cast(datefield AS varchar), '')
--
Chandu |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
|
|
rmg1
Posting Yak Master
245 Posts |
Posted - 11/29/2012 : 05:36:37
|
Thanks for the info folks.
I've just checked another table and it appears that NULL values are acceptable in this particular field. |
|
|
| |
Topic |
|
NULL dates and removing NULL fields
