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.

SQL SERVER - CONVERT Empty String To Null DateTime datenull-800x162

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)

SQL SERVER - CONVERT Empty String To Null DateTime castdate

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.

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

SQL DateTime, SQL Function, SQL NULL, SQL Scripts, SQL Server, SQL String
Previous Post
Apache2 Linux – Common Commands
Next Post
SQL SERVER – OS Threads Used by SQL Scheduler

Related Posts

Leave a Reply