Time Only

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 1^st, 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 1^st, 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

  SET @i = @i + 1;

  SET @dt = DATEADD(day, DATEDIFF(day, GETDATE(), 0), GETDATE());

END;

SELECT DATEDIFF(ms, @start, GETDATE()) - @looptime AS

  integer_manipulation;

The first part of the code measures the time it takes a loop to iterate 10,000,000 times. The second part measures the performance of the technique based on string manipulation. It runs it 10,000,000 times, and subtracts the loop time to calculate the net time associated with the calculation itself. Similarly, the third part measures the performance of the technique based on integer manipulation.

Here’s the output I got from the performance test:

string_manipulation

-------------------

22367

integer_manipulation

--------------------

7817

As you can see, it took about 22 seconds with the string manipulation technique—22 nanoseconds average for a single calculation, and about 8 seconds for the integer manipulation technique—8 nanoseconds average for a single calculation.

Cheers,

--

BG