In the past I demonstrated techniques to extract only the date or only the time from a DATETIME value that contains both date and time. I covered those in DATETIME Calculations, Part 1. I showed a few techniques to extract only the time (or more accurately, set the date to the base date January 1st, 1900). I showed a technique based on string manipulation where you first convert the event date and time value to a character string using style 114 (time only), and then you convert the character string back to DATETIME. Here’s an example applied to GETDATE:
SELECT CAST(CONVERT(CHAR(12), GETDATE(), 114) AS DATETIME);
I showed other techniques based on integer manipulation, but they were quite convoluted. Recently I got a very slick technique based on integer manipulation from Peter Larsson from Sweden. Besides being an interesting technique in terms of its logic, it also performs about three times faster than the technique based on character manipulation.
Here’s the technique applied to GETDATE:
SELECT DATEADD(day, DATEDIFF(day, GETDATE(), 0), GETDATE());
The DATEDIFF function calculates the difference in terms of days between GETDATE and the base date January 1st, 1900 represented by the integer 0. This difference (call it diff) will be a negative value. You then add diff days to GETDATE, resulting in the current time in the base date.
I used the following test to compare the performance of the two techniques:
DECLARE
@dt AS DATETIME,
@start AS DATETIME,
@looptime AS INT,
@i AS INT;
-- Measure loop time
SET @start = GETDATE();
SET @i = 1;
WHILE @i <= 10000000
BEGIN
SET @i = @i + 1;
END;
SET @looptime = DATEDIFF(ms, @start, GETDATE());
-- String manipulation
SET @start = GETDATE();
SET @i = 1;
WHILE @i <= 10000000
BEGIN
SET @i = @i + 1;
SET @dt = CAST(CONVERT(CHAR(12), GETDATE(), 114) AS DATETIME);
END;
SELECT DATEDIFF(ms, @start, GETDATE()) - @looptime
AS string_manipulation;
-- Integer manipulation
SET @start = GETDATE();
SET @i = 1;
WHILE @i <= 10000000
BEGIN