Menu
Is there a better way to select empty datetime fields than this?
SELECT * FROM `table` WHERE `datetime_field` = '0000-00-00 00:00:00' 
414
MySQL DOES accept null values for the datetime definition, but if you for some reason think otherwise and won't use a null value, consider simply using '1000-01-01' as the value and excluding rows which have that value for your bill_date column in your queries. Mysql does allow nulls in datetime fields.
To look for NULL values, you must use the IS NULL test. The following statements show how to find the NULL phone number and the empty phone number: mysql> SELECT * FROM my_table WHERE phone IS NULL; mysql> SELECT * FROM my_table WHERE phone = ''; See Section 3.3.
SELECT * FROM yourTableName WHERE yourSpecificColumnName IS NULL OR yourSpecificColumnName = ' '; The IS NULL constraint can be used whenever the column is empty and the symbol ( ' ') is used when there is empty value.
Try using the function IS_SPACES(). If your source also has NULL values then use IS_NULL() function and route the data accordingly. Adding to what Pallavi and Prashant mentioned, there are two actions you will need to do, Identify the NULLS and replace it with an absolute date value or NULL.
Better in what way? That query does everything you ask of it and, provided there's an index on datetime_field, it's as fast as it's going to get.
If you're worried about the query looking "ugly", don't be. Its intent is quite clear.
The only possible improvement you could consider is to use NULLs for these zero-date/time rows, in which case your query becomes:
SELECT * FROM `table` WHERE `datetime_field` IS NULL That's what I'd do with a standards-compliant DBMS. My understanding is that MySQL may represent true NULL datetime fields in this horrific manner, in which case I guess the IS NULL may not work.
177
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
