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

Reportboy2021 1 Reputation point
2022-02-14T18:11:40.687+00:00

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.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2022-02-14T18:28:40.453+00:00

    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
  2. Reportboy2021 1 Reputation point
    2022-02-14T19:07:51.843+00:00

    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 100.9K Reputation points MVP
    2022-02-14T22:32:23.847+00:00

    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
  4. LiHong-MSFT 10,046 Reputation points
    2022-02-15T03:33:59.37+00:00

    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,
    LiHong

    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.