How to get Time from DateTime format in SQL?

Extracting Time from DateTime in SQL: A Comprehensive Guide

Learn various methods to precisely extract only the time component from a SQL Server DATETIME or DATETIME2 column, from simple conversions to advanced formatting.

Working with dates and times in SQL Server is a common task. Often, you might store a combined date and time in a DATETIME or DATETIME2 column, but only need to retrieve the time portion for display, comparison, or further processing. This article explores several robust methods to achieve this, catering to different SQL Server versions and specific requirements.

Understanding SQL Server Date/Time Types

Before diving into extraction methods, it's crucial to understand the primary date/time data types in SQL Server:

• DATETIME: Stores both date and time, with a precision of 3.33 milliseconds. Range from January 1, 1753, to December 31, 9999.
• DATETIME2: Offers higher precision (up to 100 nanoseconds) and a larger date range (January 1, 0001, to December 31, 9999). It's generally recommended over DATETIME for new development.
• DATE: Stores only the date part.
• TIME: Stores only the time part, with a precision of 100 nanoseconds.

Our goal is to convert a DATETIME or DATETIME2 value into a TIME type or a formatted string representing only the time.

Process Flow for Time Extraction in SQL Server

Method 1: Using CAST or CONVERT to TIME (SQL Server 2008+)

The simplest and most efficient way to get the time component in SQL Server 2008 and later is to CAST or CONVERT the DATETIME or DATETIME2 value directly to the TIME data type. This method preserves the precision of the time component without needing string manipulation.

DECLARE @MyDateTime DATETIME2 = '2023-10-27 14:35:59.1234567';

-- Using CAST to TIME
SELECT CAST(@MyDateTime AS TIME) AS ExtractedTime_CAST;

-- Using CONVERT to TIME (functionally identical here)
SELECT CONVERT(TIME, @MyDateTime) AS ExtractedTime_CONVERT;

-- Example with DATETIME
DECLARE @MyOldDateTime DATETIME = '2023-10-27 09:00:00.000';
SELECT CAST(@MyOldDateTime AS TIME) AS ExtractedTime_OldDateTime;

Extracting time using CAST and CONVERT to TIME data type.

Method 2: Using the FORMAT Function (SQL Server 2012+)

For SQL Server 2012 and newer, the FORMAT function provides a highly flexible way to format date and time values into strings. You can specify a custom format string to extract just the time portion in various styles (e.g., 12-hour, 24-hour, with or without seconds/milliseconds).

DECLARE @MyDateTime DATETIME2 = '2023-10-27 14:35:59.1234567';

-- 24-hour format (HH:mm:ss)
SELECT FORMAT(@MyDateTime, 'HH:mm:ss') AS FormattedTime_24hr;

-- 12-hour format with AM/PM (hh:mm tt)
SELECT FORMAT(@MyDateTime, 'hh:mm tt') AS FormattedTime_12hr;

-- With milliseconds
SELECT FORMAT(@MyDateTime, 'HH:mm:ss.fff') AS FormattedTime_Ms;

-- Using a standard format specifier (e.g., 'T' for short time)
SELECT FORMAT(@MyDateTime, 'T') AS FormattedTime_Short;

Using the FORMAT function for custom time string extraction.

Method 3: Using CONVERT with Style Codes (All SQL Server Versions)

For older SQL Server versions or when you need a specific string format without FORMAT function, CONVERT with style codes is a versatile option. Although it's primarily designed for DATETIME to VARCHAR conversion, some style codes effectively isolate and format the time part.

DECLARE @MyDateTime DATETIME = '2023-10-27 14:35:59.123';

-- Style 8: HH:mm:ss (24-hour)
SELECT CONVERT(VARCHAR(8), @MyDateTime, 8) AS Time_Style8;

-- Style 108: HH:mm:ss (24-hour)
SELECT CONVERT(VARCHAR(8), @MyDateTime, 108) AS Time_Style108;

-- Style 114: HH:mm:ss:mmm (24-hour with milliseconds)
SELECT CONVERT(VARCHAR(12), @MyDateTime, 114) AS Time_Style114;

-- Using custom string manipulation (less recommended for modern SQL)
SELECT SUBSTRING(CONVERT(VARCHAR(20), @MyDateTime, 120), 12, 8) AS Time_Substring;

Extracting time using CONVERT with various style codes.

While effective, using CONVERT with style codes for time extraction can sometimes feel less intuitive than CAST to TIME or FORMAT. The choice of style code depends on the exact output format desired. For a full list of style codes, refer to the SQL Server documentation on CONVERT.

Choosing the Right Method

The best method depends on your SQL Server version and specific requirements:

• SQL Server 2008+ and need TIME data type: Use CAST(YourDateTime AS TIME). This is the most efficient and type-safe approach.
• SQL Server 2012+ and need specific string formatting: Use FORMAT(YourDateTime, 'your_format_string'). Offers great flexibility for presentation.
• Older SQL Server versions or specific VARCHAR output: Use CONVERT(VARCHAR, YourDateTime, style_code). Be mindful of the style codes and their outputs.

Always consider performance implications, especially with very large datasets. CAST to TIME is generally the fastest, while FORMAT can be slower due to its CLR dependency.

1. Step 1

Identify the SQL Server version you are working with.

2. Step 2

Determine if you need the time as a TIME data type or a formatted VARCHAR string.

3. Step 3

If TIME data type is sufficient (SQL Server 2008+), use CAST(YourColumn AS TIME).

4. Step 4

If specific string formatting is required and you are on SQL Server 2012+, use FORMAT(YourColumn, 'HH:mm:ss') or similar patterns.

5. Step 5

For older SQL Server versions or specific VARCHAR outputs, consult CONVERT style codes like 108 or 8.