CODEWITHSUNDEEP
phpsqldatetimenullstrtotime
ADM
ADM

Reputation: 1610

PHP: 01/01/1970 how to store a null or empty in a SQL datetime field

I need you wisdom.

I have a form. This form has a field to enter a date. The data from this form is stored in a table where it waits for approval and when the information from this table is approved goes to the definitive table.

The data from the form is stored field by field in the pending for approval table. The field where the pending for approval date value is stored is a varchar null. The field where the definitive date is stored is a datetime null.

Now, when no data is entered in the form’s date field I get a 01/01/1970 in both tables.

The procedure that updates the definitive table has: 

ALTER PROCEDURE [dbo].[myprocedure]
  @id INT = NULL
 ,@mydate VARCHAR(8000) = NULL

AS
 SET NOCOUNT OFF;

BEGIN TRY

    UPDATE  dbo.definitive SET 
    mydate = CASE WHEN @mydate IS NULL THEN mydate ELSE CONVERT(DATETIME, @mydate, 103) END

    WHERE ( id= @id)

What do I have to do to get a null or empty in the tables if there’s no data entered in the form’s date field?

Thanks a lot!

Upvotes: 0

Views: 1882

Answers (2)

Sven
Sven

Reputation: 70853

You do not get a value "NULL" from a HTML form. You get only strings. If a field is not filled, you get an empty string.

Your NULL detection should try to insert NULL if the field is an empty string, but only if you decided that empty strings are otherwise invalid for this field. I would probably not use VARCHAR NULL for the intermediate table at all if it's purpose is to store the form data as original as can be. The form will send empty strings, so store them as empty strings without NULL as a value.

Your form processing before inserting into the final table should validate the date and set it to NULL if invalid (and not fixable) or not given.

Upvotes: 0

Jaay
Jaay

Reputation: 2153

You should check your table fields structure to ensure that null value is allowed (not sure if it is available for a date format and for your SQL server). Other thing is the date 01/01/1970 is equivalent to the timestamp 0 wich can be assimilated to a null value for a date, so you can easily manage that i guess

Upvotes: 1

Related Questions