SQLTeam.com | Weblogs | Forums

Issue with dates



I have created a form where the user can enter dates (or not).
If the dates are null, then I set the date value to '0000-00-00'
When I execute the sql stmt, the date is saving as '0000-00-00' no matter what the value.

Here is an example of my code:
sR1_Eff_Date = _POST["R1_Eff_Date"];
if ($sR1_Eff_Date == NULL)
$sR1_Eff_Date = '0000-00-00';

I know this is partly php, but I'm trying to get the values from the form into the appropriate variables then execute an insert insert command.


If this is Microsoft SQL Server then you can't store "0000-00-00" as a date - its not a valid date.

If MySQL then I believe you can - along with other invalid dates such as 30-Feb maybe?. (This is a Microsoft SQL Forum, so folk here may not be familiar with other flavours of SQL)

If the date is NULL then I would just leave it as NULL in the database - to my way of thinking its a form field which has an unknown value, and thus NULL is indeed the correct value for it. I definitely would not use a bogus value for the date ... if you need a value for "User consciously left this form field blank" (as distinct from "User has not yet seen / considered this field") then I would add a BIT column for that, rather than using a bogus value.