How to Convert Time Data from Strings in SQL Server

Problem

You’d like to convert a string containing a date and time to a TIME value in SQL Server.

Example

We need to convert a string containing a weekday name, date, and time to a TIME value.

Solution 1: Using PARSE() function

We’ll use the PARSE() function. Here’s the query you would write:

SELECT 
  PARSE('Sunday, 2 February 2020 11:23:11.1134505' AS TIME) 
  AS time_value;

Here is the result:

11:23:11.1134505

Discussion

Use the PARSE() function to convert a string containing a weekday name, date, and time to the TIME data type. This function takes the string to convert, the keyword AS, and a new data type (in our example, TIME). The string should contain a value which represents this data type. In our example, the string 'February 2, 2020 11:23:11.1134505' stores a time value.

Notice that the time is only part of this string, which also contains the date and the name of the week day. In this case, you can’t use the CONVERT() or CAST() functions; they also return a TIME data type, but without the day of the week.

If the string provided in the language that’s different from the server language, include the keyword USING and the appropriate culture code. For example, for Polish you’d write:

SELECT 
  PARSE('Niedziela, 2 lutego 2020 11:23:11.1134505' AS TIME USING 'pl-PL' ) 
  AS time_value;

For more about culture parameter values, see the official SQL Server documentation.

In this case, you should use the PARSE() function, even though it doesn’t have the best performance. If you’re operating on a string representing a time value that doesn’t store additional data (like the name of the week day), use the CASE() function. Here is an example.

Solution 2: Using CAST() function

SELECT 
  CAST('2 February 2020 11:23:11.1134505' AS TIME) 
  AS time_value;

Here is the result:

11:23:11.1134505

The string containing the date and time to convert must be in the format of the T-SQL date and time data type. You can read more in the SQL Server documentation.

The CAST() function is ANSI SQL Standard and its performance is better than CONVERT() or PARSE().

Finally, you can also use the CONVERT() function. Look at the next example.

Solution 3: Using CONVERT() function

SELECT 
  CONVERT(TIME, '2 Feb, 2020 11:23:11.1134505') 
  AS time_value;

The result:

11:23:11.1134505

The CONVERT() function takes three arguments: the new data type, the string to convert, and the (optional) desired format. However, you should only use this function if you need to specify how the date and time data should be formatted, because its performance is not as good as CAST().