SQLTeam.com | Weblogs | Forums

Issue with dates


#1

Hi

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.


#2

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.