How to CONVERT Empty String To Null DateTime in SQL?

You can use the ISNULL function in SQL to convert an empty string to a null DateTime value:

Assuming you are using SQL Server, you can use the TRY_CONVERT() function to convert an empty string to a NULL value for a DATETIME data type.

Here’s an example:

SELECT TRY_CONVERT(DATETIME, '', 120) AS converted_datetime

In this example, the TRY_CONVERT() function is used to convert the empty string to a DATETIME data type using the style code 120. The 120 style code represents the ISO 8601 date format, which is a commonly used format for representing dates and times.

If the empty string can be converted to a DATETIME value, the function returns the converted value. Otherwise, it returns NULL.

Note that the TRY_CONVERT() function was introduced in SQL Server 2012. If you are using an earlier version of SQL Server, you can use the CONVERT() function with a CASE statement to achieve the same result.

Here’s an example:

SELECT 
  CASE WHEN '' = '' THEN NULL ELSE CONVERT(DATETIME, '', 120) END AS converted_datetime

In this example, the CASE statement is used to check if the string is empty. If it is, the statement returns NULL. If it is not empty, the CONVERT() function is used to convert the string to a DATETIME value using the 120 style code.

You can use the ISNULL function in SQL to convert an empty string to a null DateTime value:

UPDATE table_name
SET date_column = ISNULL(NULLIF(date_column, ''), NULL);

The NULLIF function is used to return NULL if the value in date_column is an empty string. The ISNULL function then takes the result of NULLIF and replaces it with NULL if it is still NULL.

Here’s an example of how you could use the ISNULL function in a UPDATE statement in SQL to convert an empty string to a null DATETIME value in a table named customers:

UPDATE customers
SET date_of_birth = ISNULL(NULLIF(date_of_birth, ''), NULL);

This statement updates the date_of_birth column in the customers table and replaces any empty strings with NULL values. The NULLIF function is used to return NULL if the value in date_of_birth is equal to an empty string, and the ISNULL function takes the result of NULLIF and replaces it with NULL if it is still NULL.

Here is a much simpler way to do –SQL to convert an empty string to a null DateTime value

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

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.

What is Looker BI?

How Looker Writes SQL?

How to calculate percent of total in looker?

Table Calculations in Looker

What are Derived tables in Looker?

How to set conditional formatting in LOOKER?

Looker Interview Questions And Answers

About Me:-
I am Om Prakash Singh – Data Analytics Consultant , Looker Consultant , Solution Architect .
I am Highly analytical and process-oriented Data Analyst with in-depth knowledge of database types; research methodologies; and big data capture, manipulation and visualization. Furnish insights, analytics and business intelligence used to advance opportunity identification.

You’ve got data and lots of it. If you’re like most enterprises, you’re struggling to transform massive information into actionable insights for better decision-making and increased business results. Reach out to us here if you are interested to evaluate if Looker is right for you or any other BI solution.

How to CONVERT Empty String To Null DateTime in SQL?