How to Insert Null into the Datetime Coulmn Instead 1900-01-01 00:00:00.000 in SQL Server

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, '')

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', ..);

Blank values in Date column returning as 1900/01/01 on running SELECT statement

You dont need to do the string manipulation as you have shown in your question. If you have dates stored in mm/dd/yyyy format just cast it as DATE.

SELECT cast(a.[PAYOFF DATE] AS DATE) 
FROM MTG a 

For 1900-01-01 values, since you are converting from a string data type to Date, String datatype can have Empty strings but Date datatype cannot have empty date values, It can have either a date value or NULL value.

Therefore you need to convert the empty string to nulls before you convert it to date. 1900-01-01 is just a default value sql server puts in for you because Date datatype cannot have an empty value.

You can avoid having this sql server default value by doing something like this.

SELECT cast(NULLIF(a.[PAYOFF DATE],'') AS DATE) 
FROM MTG a 

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.

Cannot insert null into datetime in sql server

To see all the triggers in the database, use the following query:

select 
    t.name as [TableName], 
    tr.name as [TriggerName], 
    m.[definition]
from sys.triggers tr
join sys.sql_modules m on m.object_id = tr.object_id
join sys.tables t on t.object_id = tr.parent_id

To see all the default constraints, use the following query:

select 
    t.name as [TableName],  
    c.name as [ColumnName], 
    dc.[name] as [ConstraintName], 
    dc.[definition] 
from sys.tables t
join sys.columns c on c.object_id = t.object_id
join sys.objects do on do.object_id = c.default_object_id
join sys.default_constraints dc on dc.object_id= do.object_id

SQL Server inserting Date as 1/1/1900

You have not given it as null, you're trying to insert an empty string (''). You need:

INSERT INTO [ABC] ([code],[updatedate],[flag],[Mfdate]) 
VALUES ('203', '6/12/2013','N/A', NULL) 

Although really, if you're going to be inserting dates, best to insert them in YYYYMMDD format, as:

INSERT INTO [ABC] ([code],[updatedate],[flag],[Mfdate]) 
VALUES ('203', '20130612','N/A', NULL) 

---

Related Topics