Date Column with NULL value

Question

Date Column with NULL value

TriggerMe

SSChampion

Points: 11752
More actions
January 27, 2005 at 7:38 pm

#76225

What is the best way to treat a column when some of the values are NULL.
Example, I have a table that has the following records
SequenceId StartDate                                              EndDate
----------- ------------------------------------------------------ ------------------------------------------------------
1           2000-01-01 00:00:00.000                                2005-01-28 00:00:00.000
2           2000-01-01 00:00:00.000                                2005-01-27 00:00:00.000
3           NULL                                                   2005-01-27 00:00:00.000
If I perfrom the following query, it will only return the first 2 records. How do I calculate StartDate < EndDate when the StartDate could potentially be NULL?
SELECT * FROM xyz
WHERE StartDate < EndDate
Kindest Regards,

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

It's a bit difficult to believe that you would ever have a NULL StartDate with a valid EndDate. Normally, it's the other way around. But, no worries...

For NULL StartDates, I would treat them as if they were '01/01/1900' and I would treat NULL EndDates as if they were '12/31'9999'

SELECT * FROM xyz

WHERE ISNULL(StartDate,'01/01/1900') < ISNULL(EndDate,'12/31/9999')

Do be advised that this method may not use an index on either date...

--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)

TriggerMe SSChampion Points: 11752 More actions · Answer 2

The data integrity in this Database is up to sh*t. That is why the StartDate potentially has a NULL value.

I have just tried your solution and it works.

Thanks.

Kindest Regards,

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Yup... I know the feeling... you should see the one at work. Glad to help.

--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)