1900-01-01 not populating in Date column in SQL Server instead column populating as NULL

Reportboy2021 1 Reputation point

In source, DOB Date type having value as "1900-01-01" and In Datamart (Target) DOB type as "Date" but inserting as "NULL" instead of "1900-01-01" into table in SQL Server 2016 version. I wanted to insert same as source like "1900-01-01". Thanks in advance.

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,575 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Tom Phillips 17,716 Reputation points

    What is "but inserting as NULL"?

    Most likely you are looking at something else which is defaulting the value to 1900-01-01 and not actually the value in the database.

    1 person found this answer helpful.
    0 comments No comments

  2. Reportboy2021 1 Reputation point

    Hi Tom,
    Thanks for the response.
    I'm not looking for defaulting the value as "1900-01-01". It is nullable column only. what ever the data is there in source I need to populate the same. In Source I have value as "1900-01-01" in DOB column and also i have NULL value in DOB column and same I need to store in target as well.

  3. Erland Sommarskog 103.2K Reputation points MVP

    This a complete guess, but maybe you stored procedure has something which goes nullif(DOB, ''). The meaning of this is that "if DOB is blank, return NULL". However, being blank is a property of a strings, and not of dates - a date can never be blank. But in SQL Server there is an implicit conversion of strings to dates, and the way it works is that en empty string converts to, yes, you guessed it, 1900-01-01. Thus, if DOB has the value 1900-01-01, the nullif function will return NULL for this date.

    Personally, I would much prefer to have NULL than 1900-01-01.

    0 comments No comments

  4. LiHong-MSFT 10,046 Reputation points

    Hi @Reportboy2021
    First , why there is such a weird date as '1900-01-01'?
    A datetime column cannot be a blank. It is either a NULL or a valid date time. If insert /update it with a blank , you will get (as you know) 1900-01-01 00:00:00.000.
    When the conversion is from time to datetime, the time component is copied, and the date component is set to '1900-01-01'.
    Please refer to this document for more details.

    So '1900-01-01' is not a date that actually exists, its real meaning is blank. This is why only "1900-01-01" becomes NULL and other dates are inserted normally.
    If you examine the script of your stored procedure, you might find the code that means "if 1900-01-01, then set to null", maybeNULLIF() or case when '1900-01-01' then null ,etc.

    Then, if you want to change all NULLs to '1900-01-01', just change the script of your stored procedure. Or use update statement : update tablename set DOB='1900-01-01' where DOB is null

    Best regards,

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.