Insert NULL into DATE field MySQL 5.6

Question

I seem to be running into an issue inserting NULL into a DATE field in MySQL 5.6. The error is Error Code: 1292. Incorrect date value: '' for column 'StartDate' at row 1.

Here is the table description. The StartDate and EndDate are both DATE type, NULLABLE and default value of NULL.

Active bit(1) NO b'1'
ClientContact varchar(255) YES
ClientID int(11) unsigned NO MUL
ClientReferenceNumber varchar(100) YES
Description mediumtext YES
EndDate date YES
HourlyRate decimal(6,2) YES
Hours int(11) YES
Notes text YES
OffShoreAmount decimal(10,2) YES
OnShoreAmount decimal(10,2) YES
ParentWorkOrderID int(11) unsigned YES MUL
ReferenceNumber varchar(100) YES
StartDate date YES
TotalAmount decimal(10,2) YES
WorkOrderID int(10) unsigned NO PRI auto_increment WorkOrderTypeID int(10) unsigned NO MUL

However the following queries do NOT work.

INSERT INTO WorkOrder
    (ParentWorkOrderID, WorkOrderTypeID, ClientID, ReferenceNumber, ClientReferenceNumber,
    Description, StartDate, EndDate, OnShoreAmount, OffShoreAmount, TotalAmount, HourlyRate, 
    Hours, Notes, Active, ClientContact)
VALUES (NULL, 1, 89, 'et-care-001', 'HG453443', '', '', '', 10, 0, 10, NULL, NULL,
        '', 1, 'Jebus');

INSERT INTO WorkOrder
    (ParentWorkOrderID, WorkOrderTypeID, ClientID, ReferenceNumber, ClientReferenceNumber,
    Description, StartDate, EndDate, OnShoreAmount, OffShoreAmount, TotalAmount, HourlyRate, 
    Hours, Notes, Active, ClientContact)
VALUES (NULL, 1, 89, 'et-care-001', 'HG453443', '', '', '', 10, 0, 10, 'NULL', 'NULL',
        '', 1, 'Jebus');

I'm positive this was working in MySQL 5.5. What is the proper way to insert null into the field?

You're not inserting NULL, you're inserting an empty string. — shmosel
Ahhh you are right, something happened to the PHP code during a merge and the values and columns was in the wrong order — greyfox

bear bear · Accepted Answer · 2014-06-24T16:23:24

Insert NULL and not ''. Also, 'Null' is not equal to NULL

INSERT INTO WorkOrder (
    ParentWorkOrderID, 
    WorkOrderTypeID, 
    ClientID, 
    ReferenceNumber, 
    ClientReferenceNumber,   
    Description, 
    StartDate, 
    EndDate, 
    OnShoreAmount, 
    OffShoreAmount, 
    TotalAmount, 
    HourlyRate,  
    Hours, 
    Notes, 
    Active, 
    ClientContact
    ) VALUES (
    NULL, 
    1, 
    89, 
    'et-care-001', 
    'HG453443', 
    '', 
    '',         
    '', 
    10, 
    0, 
    10, 
    NULL, 
    NULL,
    NULL, 
    1, 
    'Jebus');

Insert NULL into DATE field MySQL 5.6

2 Answers