Show Date and Time as Blank instead of 1900-01-01

Last updated: 12th July 2025

In SQL Server, the DATE and DATETIME data types are commonly used to store date and time information within tables. However, a frequent challenge arises when the default value 1900-01-01 is automatically inserted, which can lead to confusion and misrepresentation of data. Since this default date can be misleading, especially when displayed in reports or user interfaces, it’s better to avoid using it altogether. A reliable alternative is to store a NULL value in the column instead, indicating that no valid date has been provided. This approach ensures cleaner, more accurate data handling and display.

Let's create a table and insert some values in it.

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

INSERT INTO Product_List (ProdCode, Product) VALUES (1, 'BOOKS')
SELECT *FROM Product_List

Figure 1

The "Product_List" table contains three columns, including one defined with the datetime data type. Based on the insert statement provided above 👆, if no value is supplied for the DateEdited column during data insertion, SQL Server automatically stores a NULL value. This behavior occurs because the column was explicitly configured with a default value of NULL during table creation. Using NULL instead of a placeholder like 1900-01-01

Value as Blank

Occasionally, developers try to push a blank value in the date column, which results in translating and inserting a 1900-01-01 00:00:00. The value 'Blank' (Single Quote with no value) is meant for data types which accept character data (like VARCHAR or CHAR), it’s not recognized by date or datetime data types. For example:

INSERT INTO Product_List (ProdCode, Product, DateEdited) VALUES (1, 'BOOKS', '')
SELECT *FROM dbo.Product_List

Figure 2

date time showing 1900-01-01

Since we cannot insert a blank value in place of "1900-01-01", we can use a workaround to display a blank value instead of an outdated date. We will write a simple query to convert 1900-01-01 into a more meaningful value.

The SQL Query

SELECT DateEdited = 
    CASE WHEN DateEdited = '1900-01-01 00:00:00.000' THEN '' 
    ELSE CONVERT(VARCHAR(10), DateEdited, 103) 
    END
FROM Product_List

The SQL query shown above converts the date to a string value. The CONVERT() function will translate the value in a string format. As mentioned earlier, blank values ('') are valid in string-based data types like VARCHAR, making this technique useful for displaying a blank instead of misleading or default dates within user-facing applications.

Conclusion

This workaround serves as a practical solution for controlling how blank dates are displayed, especially when dealing with legacy defaults like 1900-01-01. While it’s not a perfect fix, since true blanks could be represented as either "NULL" or "1900-01-01" depending on the context, it effectively prevents inaccurate or irrelevant information from reaching end users. By converting date values into string formats, developers gain greater control over display logic and UI clarity. Ultimately, because date and time data types are non-character values, it’s crucial to handle them deliberately and consistently to preserve data accuracy across applications.

← PreviousNext →

Show Date and Time as Blank instead of 1900-01-01 - SQL Server

Value as Blank