return value for NULL fields when using date_format

[connectionscentres]

Hi,

I cant find the answer on the manual pages. Maybe I should look harder.

Basically I'm putting dates into a table using:

INSERT INTO TEMP date_format(INVOICEDATE, '%Y-%m-%d %H:%i:%s') As datei

then I can select dates that have put an INVOICEDATE in by doing:

SELECT * FROM TEMP WHERE datei>'1'

Only the reverse doesnt work, meaning I cant do a query for rows where no invoice date was entered (i.e. null)

So I need to know exactly what value the date_format function returns when it comes accross a null value. I've tried echoing the result to the screen and it returns an empty string but then doing a

SELECT * FROM TEMP WHERE datei NOT LIKE ''

doesnt work. Any suggestions?

 

[sleipnir214]

There is a difference in MySQL between a NULL value and an empty column (see

http://dev.mysql.com/doc/mysql/en/problems-with-null.html).

As an aside, I don't think that "As datei" in your INSERT query has any meaning whatsoever.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[r937]

i'd be interested in seeing the full queries, and the reason for the temp table

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)