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

Last updated: 17th June 2024

SQL Server DATE and DATETIME data types store date and time values in a table. However, managing these values can become difficult sometimes, especially if the value saved is 1900-01-01. Moreover, we cannot show or display these figures anywhere, since it can be misleading. However, there is one way to avoid saving 1900-01-01 in a table and instead save the value Null in the table.

Let's create a table first.

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 table "Product_List" consists of three columns, with one of them being of the datetime data type. Referencing the insert statement above👆, when values are inserted into the table, no value has been specified for the DateEdited column. As a result, a "NULL" value is inserted for this column. This occurs because, during the table's creation, a default value was set for the DateEdited column, which is NULL.

Value as Blank

But sometimes we 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, but it is not recognized by the date or datetime data types.

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

Figure 2

Since we cannot insert a blank value in place of "1900-01-01", we will 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 query above will actually convert the date to a string value. The CONVERT() function will translate the value in a string format. And as we have mentioned above that a blank is accepted as a string value.

Conclusion

The solution is more of a workaround or a quick fix rather than a complete solution, since a blank date can be either "NULL" or "1900-01-01". However, converting the date into a string helps us avoid showing irrelevant data to our clients. As Date and Time are not character values, it is essential to handle them with care.

← PreviousNext →

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

Value as Blank

Related Articles