Insert Null/Empty Value in SQL Datetime Column by Default

Insert null/empty value in sql datetime column by default

if there is no value inserted, the default value should be null,empty

In the table definition, make this datetime column allows null, be not defining NOT NULL:

...
DateTimeColumn DateTime,
...

I HAVE ALLOWED NULL VARIABLES THOUGH.

Then , just insert NULL in this column:

INSERT INTO Table(name, datetimeColumn, ...)
VALUES('foo bar', NULL, ..);

Or, you can make use of the DEFAULT constaints:

...
DateTimeColumn DateTime DEFAULT NULL,
...

Then you can ignore it completely in the INSERT statement and it will be inserted withe the NULL value:

INSERT INTO Table(name, ...)
VALUES('foo bar', ..);

How to insert NULL into the DATETIME coulmn instead 1900-01-01 00:00:00.000 in SQL Server

When you set a datetime variable to an empty string an implicit conversion happens. And an empty string converts implicitly to 1900-01-01. Either just declare the variable and don't assign them a value or explicitly set them to NULL.

@DATEVAL1 DATETIME,
@DATEVAL2 DATETIME = NULL

Or you could use NULLIF. Since you are receiving these values from an API you would most likely have to use NULLIF. Otherwise a change to the API call would be required to send in a NULL instead of an empty string.

Values( NULLIF(@DATEVAL1, '')

Inserting null when variable is empty for DATETIME

using a small crystal ball here, but I believe the OP is passed an empty string ('') to the datetime parameter, not a NULL. These are two very different values; as '' will result in the datetime 0 (which is 1900-01-01').

If this guess is correct, then you could use NULLIF:

ALTER PROCEDURE [dbo].[DBK_spDataUpdate] @FirstName    AS NVARCHAR(250)= NULL,
                                         @FillDate     AS DATETIME = NULL,
                                         @DataID       AS BIGINT AS
    SET NOCOUNT ON;
    UPDATE      dbo.DBK_tbData
    SET         FirstName            = @FirstName,
                FillDate             = NULLIF(@FillDate, '19000101')
    WHERE       DataID               = @DataID;

Otherwise, instead of passing an empty string, pass a NULL.

Error #1265 - Can't insert an empty value in a default 'none' field

In MySQL/MariaDB date fields cannot contain empty string. By default, without SQL strict mode, they would convert them to a zero date, which would still be a date albeit invalid, and not empty string. You can set the date field to NULL, and pass NULL if the value is unknown.

Since MariaDB version 10.2.4, they have enabled SQL strict mode by default, which no longer allows empty dates to be inserted into DB.
Read more about SQL strict mode

If strict mode is enabled, '0000-00-00' is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, '0000-00-00' is permitted and inserts produce a warning.

Check your MariaDB SQL mode with this SQL through phpMyAdmin: SELECT @@global.sql_mode;
If the settings contain either STRICT_ALL_TABLES or STRICT_TRANS_TABLES then strict mode is enabled.

How to insert null value in datetime column of mysql in c#

You Can use ISNULL Property in MySQL

ISNULL

Like this

ISNULL(levdat,'')

Try this

DELIMITER $$

 DROP PROCEDURE IF EXISTS `db_school_management`.`ins_tch`$$

 CREATE PROCEDURE `db_school_management`.`ins_tch`
     (citycod INT,_statecod INT,_tchfstnam       VARCHAR(50),_tchlstnam VARCHAR(50),_tchfatnam   VARCHAR(50),
    _tchmotnam VARCHAR(50),_tchdob DATETIME,_tchgen VARCHAR(5),_tchadd VARCHAR(200),_tchmob VARCHAR(20),_tchphn VARCHAR(20),_tchzipcod VARCHAR(10),
    _tchedu VARCHAR(50),_tchsal INT,_tcheml VARCHAR(50),_tchusrnam VARCHAR(50),_tchpwd VARCHAR(50),_tchjoindat DATETIME,_tchpic VARCHAR(50),_tchlevdat DATETIME,_flag VARCHAR(5))
 BEGIN
    INSERT INTO tbteacher(citycod,statecod,tchfstnam,tchlstnam,tchfatnam,tchmotnam,tchdob,tchgen,tchadd,tchmob,tchphn,tchzipcod,tchedu,tchsal,
    tcheml,tchusrnam,tchpwd,tchjoindat,tchpic,tchlevdat,flag) VALUES(_citycod,_statecod,_tchfstnam,_tchlstnam,_tchfatnam,_tchmotnam,ISNULL(_tchdob,''),_tchgen,        _tchadd,_tchmob,_tchphn,_tchzipcod,_tchedu,_tchsal,_tcheml,_tchusrnam,_tchpwd,_tchjoindat,_tchpic,ISNULL(_tchlevdat,''),_flag);
 END

---

Related Topics