Hide null datetime values - 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

Development Tools

Reporting Services Development

Hide null datetime values

Author

Topic

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2007-09-13 : 18:06:22

I have a dataset with a datetime column. Not all of the rows have a date and time entered so some have nulls.

I want to display all the rows but not show anything in the datetime field if the value is null.

How do I do this? I've tried numerous things but none have worked. When I run the query that is the datasource for the dataset Dec 30 1899 12:00 AM is displayed. That is what I want to hide.

Thanks in advance,

Kevin

Kristen
Test

22859 Posts

Posted - 2007-09-13 : 18:27:32

The value for the column when it is null will be NULL (i.e. it won't be some specific, bogus, date). Your application language will have some means of testing for NULL - perhaps IsNull.

So you should be able to create a function something like:


Function fnMyDateDisplay(RecordSet, DateColumnName)
If IsNull(RecordSet(DateColumnName)) Then
Return ""
Else
Return fnDateFormat(RecordSet(DateColumnName)) ' Assuming you need the date formatted
End If
End Function

and then wrap any Date Column display using that function.

Kristen

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-09-13 : 18:27:39

Dec 30 1899 12:00 AM is not null. It is a date value.
Replace these values in your data with actual NULLs.

e4 d5 xd5 Nf6

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 18:29:17

That is "DATE ZERO" for Microsoft Access.

IIF(RecordSet(DateColumnName) = 0, '', RecordSet(DateColumnName))

E 12°55'05.25"
N 56°04'39.16"

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2007-09-13 : 18:33:19

Thanks for the replies. I should have known that this was a date value being returned. I think I've figured it out. I'm querying a FoxPro linked server and used the following to replace these values with NULLS.

IIF(EMPTY(columnname),NULL,columnname)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources