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

sql server

← PrevNext →

Tools you can use

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

Related Post: How to Insert multiple rows with a single INSERT statement in SQL Server

Figure 1

null value for date

Table Product_List has 3 columns and one of the columns has the datetime data type. Take a good look at the insert statement (above). When inserting values in the table I have not mentioned any value for the DateEdited column. This will insert a NULL value for the column, since while creating the table I have set a default value for the date column, and it is NULL.

You may like this post 👉: What is the use of CHARINDEX function in SQL Server - it has an example you don't want to miss.

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 Product_List

Figure 2

date show 1900-01-01

Since, we cannot insert a blank value instead of 1900…, we will use a workaround so we can at least display a blank value instead of a value that shows the date from another era. We will write a simple query, which will convert 1900-01-01 into a 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.

This is for the beginners (and its useful): Why use Joins in SQL Server and how it can Speed up data search?

Conclusion

The solution is in-fact a workaround or a quick fix than a total solution, since a blank date can be either a NULL or 1900-01-01. However, converting the date into a string does relieve us from showing irrelevant data to our clients. Date and Time are not character values and therefore, you must handle it carefully.

Thanks for reading. ☺

← PreviousNext →