Identifying NULL Dates

Question

Post reply

Identifying NULL Dates

scking

SSCrazy

Points: 2598
More actions
May 12, 2005 at 11:15 am

#164438

I'm writing a TSQL procedure using a CASE statement to display a NULL date represented as '1900-01-01 00:00:00.000' as a '' string but having no success.
I've tried IS NULL, direct comparisons, using DATEDIFF and comparing against GETDATE(), and CASTing the value to nvarchar and comparing. Any help would be appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

this works :

Declare @MyDate as datetime

set @MyDate = null

select isnull(@Mydate, '1900-01-01 00:00:00.000')

scking SSCrazy Points: 2598 More actions · Answer 2

Thanks. I found another solution using ISDATE which will return a 0 for NULLs and 1 for valid dates. Then, I also discovered that unless I CAST the date variable as a varchar displaying it as an empty string '' in the CASE statement will also display it as '1900-01-01 00:00:00.000'.

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 3

Yes there is no need to have a CASE for your TSQL

Just

SELECT ISNULL(MYDATE,'1900-01-01 00:00:00.000') FROM MYTABLE, will give you the desired output.

Prasad Bhogadi
www.inforaise.com

Kemp Davis Ten Centuries Points: 1067 More actions · Answer 4

This can also be accomplished by simply:

SELECT ISNULL(MYDATE,'') FROM MYTABLE

as long as MYDATE is datetime.

Kemp

scking SSCrazy Points: 2598 More actions · Answer 5

I see that some would fall into the same situation I was in. My original post indicated I had problems because I couldn't seem to weed out the NULL value on my displays. Originally is used all the select statements for selecting NULL date values but kept getting the NULL date being displayed.

CASE WHEN datefield IS NULL THEN ''

will find the NULL value and display a NULL value. In the CASE statement the '' translates to a NULL for the display. So I was finding the NULL in the case statement and then displaying a NULL. I reasoned then that the CASE statement was not working but I was wrong. It was working but the '' displayed the date NULL value. I resolved this by using:

CASE WHEN datefield IS NULL THEN 'N/A'

I could as easily used a space.

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 6

scking,

I'm curious... what is your objection to using ISNULL or it's big brother, COALESCE? Yes, CASE works but it's probably not a fast as ISNULL... your last CASE example could easily be written as...

ISNULL(datefield,'N/A')

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)