problem with null - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

problem with null

Author

Topic

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2007-01-12 : 03:41:03

i have written a SP...
it is returning a datetime value among others..
sometimes the datetime value is null but the SP is returning the following string 01-01-1900.
i want to suppress it.
plz help

thanx and regards

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-12 : 03:44:35

It is the way SQL handles NULL value in datetime datatype. It throws a base date i.e. 01-01-1900. What do you mean by you want to suppress it? What alternate value you want to show?

You can use,

select IsNull(DateCol, 'SomeAlternateValue') from SomeTable

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2007-01-12 : 03:50:05

I seriously doubt that the value is null, empty string maybe, but not null, a conversion of a null value will also result in a null value.

It will be best to post your code and sample data here first.

I have however assumed that you have a varchar value which you cast as a datetime value and then select the value.

Something like this would supress the value in that case if it is an empty string

declare @dt datetime
declare @sdt varchar(12)
set @sdt = ''
set @dt = cast(@sdt as datetime)

select case when len(@sdt) < 1 then '' ELSE convert(varchar, @dt, 105) end

set @sdt = '2006-12-31'
set @dt = cast(@sdt as datetime)

select case when len(@sdt) < 1 then '' ELSE convert(varchar, @dt, 105) end

Duane.

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2007-01-12 : 03:57:47

i am receiving the value in vb6 and sending it to a MSExcel2000 file , which is showing all the columns returned.

i want to show a "space" or "-"(hyphen) or "_"(underscore),"N.A"(not available) etc whenever the SP returns 01-01-1900, just to show the customer that the data is not present.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-12 : 04:00:16

While fetching the data, you can use the CASE statement as Ditch shown to return alternate value instead of 01-01-1900.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2007-01-12 : 04:00:22

something like this then?
select case when len(@sdt) < 1 then 'N/A' ELSE convert(varchar, @dt, 105) end

Duane.

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2007-01-12 : 04:17:24

thanx

the select-case worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources