How to Use SQL Server Triggers to Block Default 1900-01-01 Dates

You can use a BEFORE INSERT or AFTER INSERT style trigger in SQL Server (technically it's just an AFTER INSERT, since SQL Server doesn't support BEFORE triggers) to intercept rows being inserted into a table and check whether a column contains the unwanted default date 1900-01-01. If it does, you can either raise an error to reject the insert or update the value to NULL or a more meaningful date.

Create a Table

Let's assume, this is my table. It has three columns and one defined with the datetime data type.

CREATE TABLE Product_List (
    ProdCode int NOT NULL,
    Product varchar(50) NULL,
    DateEdited datetime NULL,
)

Inserting data into table with a blank value in the datetime column will result in inserting a 1900-01-01 00:00:00.

INSERT INTO Product_List (ProdCode, Product, DateEdited) VALUES (1, 'BOOKS', '')

date time showing 1900-01-01

👉 We want to prevent this from happening.

Here’s how to do both approaches:

1) Reject Inserts with 1900-01-01

CREATE TRIGGER trg_RejectDefaultDate
ON Product_List
AFTER INSERT
AS
BEGIN
  IF EXISTS (
    SELECT 1
    FROM inserted
    WHERE DateEdited = '1900-01-01 00:00:00.000'
  )
  BEGIN
    RAISERROR ('Insertion failed: 1900-01-01 is not a valid DateEdited value.', 16, 1);
    ROLLBACK TRANSACTION;
  END
END;

Execute the trigger statement. This stops the insert and warns the user that they need to supply a valid or NULL date.

Ok, now run the insert statement again with a blank value for the datetime column and you will see this error.

using trigger to prevent 1900-01-01 dates

2) Convert 1900-01-01 to NULL Automatically

This trigger will silently replace 1900-01-01 with NULL after the row is inserted.

CREATE TRIGGER trg_ReplaceDefaultDate
ON Product_List
AFTER INSERT
AS
BEGIN
  UPDATE Product_List
  SET DateEdited = NULL
  WHERE ProdCode IN (
    SELECT ProdCode
    FROM inserted
    WHERE DateEdited = '1900-01-01 00:00:00.000'
  )
END;

💡 Remember,

• Triggers add overhead, so only use them if this logic can’t be handled in your app layer or through constraints/validation.

• If you're doing bulk inserts, the update method (Example 2) is safer than the rejection method (Example 1 - See above).

🚀 More SQL Tips

• Show Date and Time as Blank instead of 1900-01-01 - SQL Server
• How to find employees who were hired in the last n months?
• How to edit more than 200 rows in SQL Server Management Studio?
• Can an SQL query contain both "where and having" clause?

← PreviousNext →