SQL SERVER – CONVERT Empty String To Null DateTime

Let us learn today how to CONVERT Empty String To Null DateTime. While I focus on SQL Server Performance Tuning Comprehensive Database Performance Health Check, at the end of the consultancy, I like to answer any question my client has on SQL Server.

Recently, the client showed me the following code where when they try to convert the empty string to date time, it was automatically converted to year 1900-01-01 date.

SELECT CAST('' AS DATE)

When you run the script above you will get a return the date and time in the year 1900. Their existing code was actually using a case expression to convert the 1900 date to a NULL value. This was very exhaustive and negatively impacting the performance.

Null DateTime

Here is a much simpler way to do the same:

DECLARE @Var VARCHAR(4) = ''
SELECT
CAST(NULLIF(@Var,'') as DATE)

Now instead of using the CASE statement we can just the above query and get our result as NULL. I hope this quick tip helps. Let me know what you think of the same. I always like to hear from all of you. Leave a comment.

Let me know if you are interested to know more about this topic. I will write more blogs as well as create an SQL in Sixty Seconds video.

Here are my few recent videos and I would like to know what is your feedback about them.

• Bitwise Puzzle – SQL in Sixty Seconds 160
• Find Expensive Queries – SQL in Sixty Seconds #159
• Case-Sensitive Search – SQL in Sixty Seconds #158
• Wait Stats for Performance – SQL in Sixty Seconds #157
• Multiple Backup Copies Stripped – SQL in Sixty Seconds #156

Reference: Pinal Dave (http://blog.SQLAuthority.com)