Mysql fieldate type better null or 0000-00-00 as default value

Databases
1

Hi,
For field date type is better using null or 0000-00-00 as default value?
I think null should be a better default value but I’d like to know
your point.
Thanks in advance.
Bye

2

NULL is far better

but a lot depends on the semantics of the column, i.e. what kind of a date it is

3

Thanks for the reply.
Yeah I agree I ended up store the current date (today)
because of I’ve to make a query like


DATEDIFF( CURDATE( ) , user.login_date ) < 365

so if I used both null and 0000-00-00 as default value
I got
null < 365
that isn’t true
with the current date I got
0 < 365
that’s true

I don’t like very much a not fixed value
or at least not standard value
(I mean if I have to an other query o comparison
I’ve to know 2010-12-09 :frowning: )
but I’ve not other idea about it.

Feel free to speak your mind :slight_smile:

4

null isn’t equal to anything (not even to null) so if you use null in comparisons it is never going to be included in the results (that includes all comparisons including equal and not equal). There are special functions you need to use if you want to test for if the field is null.

5

Yeah I know but thanks for the point
but doing a numeric comparison if
I set null as default value I should
add an extra clause such as


WHERE user.login_date IS NULL OR DATEDIFF( CURDATE( ) , user.login_date ) < 365

mmm it’s not so bad
I hate that not fixed value :slight_smile: