Datetime NULL or 0000-00-00 00:00:00

Databases
1

Hello,

If a datetime field is not null, is it better to store a NULL value or set 0000-00-00 00:00:00 instead as a default value.

I have seen people using both approaches I just don´t know what is the best practice regarding null values in general.

Thanks :slight_smile:

2

IMHO, 0000-00-00 00:00:00 is a better choice than NULL. This is because not only null same as no value, but null can also be translated into Unknown Value. So no value plus 2 is 2. But Unknown Value plus 2 is Unknown Value. It’s just a good practice to use 0000-00-00 00:00:00.

3

that’s very sloppy logic, agmay, sorry, no offence

NULL and 0000-00-00 are ~not~ the same

my advice is: use NULL if there is no value, and stay away from “dummy values” (you can google this term for more information)

4

Hi…

If you store NULL, can certain date/time functions work incorrectly?

Will the same advise apply to INT fields, I mean is it better to use NULL than a 0?

I googled “null vs dummy values” and “null vs default values” and I came across some interesting posts, however people tend to disagree a little so maybe the choice should be based on a specific need rather than a rule of thumb, but then again you dont always know all the variables and what could go wrong. :eek:

Here are some of these posts as reference:

http://discuss.joelonsoftware.com/default.asp?design.4.34163.39
http://stackoverflow.com/questions/2116719/sql-using-null-values-vs-default-values
http://stackoverflow.com/questions/474806/null-vs-default-value-in-sql-server-database

Thanks

5

Well if the field is set as NOT NULL then you can’t insert a NULL into the field.

I still prefer the use of NULL over dummy values.

6

no, they will all work correctly

which one(s) are you worried about?

absolutely, yes

using 0 can lead to inaccuracies

7

Suppose you have a list of students and the scores they earned on a test.

Here are three marks: 50, 60, 70.

In addition to those students, one student didn’t write the test because he was away. Another one wrote the test but got every answer wrong.

In your scenario you’d enter 0 for both of them. One of those is inaccurate. Also how would you tell them apart? (i.e. which was an actual score and which a dummy value).

Now also try to average the test scores, if you have a NULL entered for the student who didn’t write the scores then you’d average the four scores (0, 50, 60, 70) and come up with 45 which would be correct. If you used a 0 for the “missing” test result then you’d come up with a class average of (0,0,50,60,70) 36 which would not be correct.

Just my two cents as an example.

8

Originally Posted by r937
which one(s) are you worried about?

Someone mentioned this to me and I quote:

If you store NULL, certain date/time functions may act incorrectly, forcing you to use COALESCE and do something like:

DATE_ADD(COALESCE(date_field, “0000-00-00 00:00:00”), INTERVAL 1 DAY)

Originally Posted by guelphdad
Just my two cents as an example.

It makes sense to avoid default values when they have no meaning such as in this case with 0000-00-00 00:00:00

So unless 0 or 0000-00-00 00:00:00 have a purpose is better to go with NULL.

Thanks to all for your posts!!