Articles Cookbook
Back to list
SQL Server

How to Extract or Convert Time Data from a String in SQL Server

Database:

Operators:

PARSE(), CAST(), CONVERT()

Problem:

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

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

Solution 1:

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 name of the week day. Don’t use the CONVERT() or CAST() functions in this case; they also return a TIME data type, but without the day of the week.

If you’re using a spoken language that’s different from the server language, include the keyword USING and the appropriate culture code. For the Polish language, 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:

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:

  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().

Recommended courses:

Recommended articles:

See also:

go to top