Umm. Selecting null date fields? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Umm. Selecting null date fields?

Author

Topic

spudhead
Starting Member

34 Posts

Posted - 2002-08-19 : 10:41:54

Hello.

On SQL Server 2000, how do you select null values in date fields? This is what I've got:

SELECT
orders.id AS orderID,
orders.pdf_path,
CONVERT(varchar(10),order_dates.date_submitted,103) AS dateSubmitted,
CONVERT(varchar(10),order_dates.date_actioned,103) AS dateActioned,
CONVERT(varchar(10),order_dates.date_completed,103) AS dateCompleted,
addresses.postcode,
quantities.quantity,
costcodes.costcode,
templates.template_name,
users.fullname
FROM orders
INNER JOIN order_dates ON orders.id=order_dates.order_id
INNER JOIN addresses ON orders.address_id=addresses.id
INNER JOIN quantities ON orders.quantity_id=quantities.id
INNER JOIN costcodes ON orders.costcode_id=costcodes.id
INNER JOIN templates ON orders.template_id=templates.id
INNER JOIN users ON order_dates.submitted_by=users.id
WHERE orders.status='requested'
AND orders.extranet_id=2
AND order_dates.date_completed=null

Now, I've tried putting 'AND dateCompleted=null', and it tells me dateCompleted isn't a valid column. I've tried putting null in single quotes and it tells me it can't convert to varchar from datetime.

Where am I being stupid?

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-19 : 10:44:54

IS NULL or IS NOT NULL! unless you have some flag set but its best to use IS NULL or IS NOT NULL!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-08-19 : 10:45:21

nothing is EQUAL to NULL.....NULL is an absence of value.....(this arguement/point of view has appeared in more eloquent forms here before)

but "IS NULL" should solve your problem....

"no quotes needed"

spudhead
Starting Member

34 Posts

Posted - 2002-08-19 : 11:13:01

Thanks for your help. I didn't know about ISNULL :)

I'm still not sure about it, though. ISNULL "Replaces NULL with the specified replacement value" - I don't want to replace it, I don't even want to select it. I just want to return all rows where it is. I've got:

AND ISNULL(dateCompleted,'nothing')

and tried

AND ISNULL(order_dates.date_completed,'1/1/2000')

but get a syntax error. Could anyone provide a quick example of the correct syntax?

Thanks.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-19 : 11:17:45

no not the ISNULL function the IS NULL comparison ...

since NULL is really not a value and the following statement is always true "NULL != NULL" and to express "NULL == NULL" in SQL you have to do "NULL IS NULL" ... and "NULL IS NOT NULL" is always false because NOT BEING NULL means any value...

WHERE orders.status='requested'
AND orders.extranet_id=2
AND order_dates.date_completed IS NULL

spudhead
Starting Member

34 Posts

Posted - 2002-08-19 : 11:36:41

Aha, that works :-)

Thanks very much everyone, I've learnt something....

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-08-19 : 11:44:12

KRYPTONITE

Voted best SQL forum nickname...."Tutorial-D"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources