[RESOLVED] SQL WHERE NULL or NOT NULL

[LingoOutsider]

I'm looking to pull information from a SQL database and have let the user select 4 different options to show only the rows related to them.

The table has two columns all based on dates (a start date and a finished date)
The user can choose to view only the startdate, only the enddate, the dates that haven't finnished (are NULL),or all the dates.

What command can i put in to my SQL search statement to find a field that has content in/not in?

[LingoOutsider]

so far...

$sqllook="SELECT * FROM enquiryform WHERE startdate IS NOT NULL AND enddate IS NOT NULL" 

This is what I have, It shows all the information even if its empty? I've not put anything in the cells and Navicat 8 tells me they are NULL?

[Hack]

SQL Database as in SQL Server Database?

What version?

[GaryMazzone]

Using SQL Server:


StartDate and EndDate between Some Date
SELECT * FROM enquiryform WHERE 'EnteredDate' BETWEEN StartDate and EndDate

StartDate But Not Completed
SELECT * FROM enquiryform WHERE StartDate IS NOT NULL and EndDate IS NULL

Started after a date
SELECT * FROM enquiryform WHERE StartDate >= 'SomeDateHere"

Enddate after a date
SELECT * FROM enquiryform WHERE EndtDate >= 'SomeDateHere"

[LingoOutsider]

MySQL 4.1

IS NOT NULL doesn't want to work, still shows row when I ask to avoid it

$sqllook="SELECT * FROM enquiryform WHERE enddate IS NOT NULL" 

[techgnome]

I think that's because dates by nature cannot be null... most DBMSs will stick in a date of something like 12/31/1899 11:59:59pm for a "null" date... it might be that NaviCat is taking this into consideration, and when it comes across the date, it interprets it as NULL....

-tg

[LingoOutsider]

So is there a way for me to Identify if a field has been asigned if i'm looking a dates?

[techgnome]

hard to say. I've used a check for "12/31/1899 11:59:59pm" before with success.... I also know that brucevde has also had success using the IS NULL syntax... go figure... I've not has a need for it in mySQL, so I'm not sure.

-tg

[kows]

I hate working with dates. I always use unix timestamps instead. if you could switch, then a "null" date would just be equal to 0, and the SQL would be simple:

#has started, but hasn't ended
SELECT * FROM table WHERE StartDate>0 AND EndDate=0

this probably won't help you unless you want to switch, though!