DATETIME Calculations, Part 1

Extracting Time Only
The techniques to extract only the time are based on logic that's similar to the logic used for extracting only the date. You zero the date portion, setting it to the base date. I'll start with a calculation that relies on the logic from the third technique in the previous section:

SELECT DATEADD(
  ms,
  DATEDIFF(
   ms,
   DATEADD(day,
     DATEDIFF(day, 0,
     GETDATE()), 0), --
     date only 
   GETDATE()),
  0);

Notice that the DATEDIFF function calculates the difference in terms of milliseconds between the date-only portion of the input date and the input date. Call it diff. The outer DATEADD function adds diff milliseconds to the base date (represented by the integer 0).

If you want accuracy in terms of seconds instead of milliseconds, simply specify a second datepart instead of millisecond (ms):

SELECT DATEADD(
  second,
  DATEDIFF(
     second, 
     DATEADD(day, DATEDIFF(day,
      0, GETDATE()), 0), -- date
      only 
     GETDATE()), 
  0);

Of course, you also have the option to use logic similar to the logic used in the first technique—except that here you'll convert the input value to CHAR(14) using style 114 (hh:mi:ss:mmm) if you're after an accuracy of milliseconds:

SELECT CAST( 
  CONVERT(CHAR(12), GETDATE(), 
     114) -- 'hh:mi:ss:mmm'
  AS DATETIME);

If you're after an accuracy of seconds, convert to CHAR(8). By doing so, you'll trim the milliseconds portion:

SELECT CAST( 
  CONVERT(CHAR(8), GETDATE(),
    114) -- 'hh:mi:ss:mmm'
  AS DATETIME);

Only the Beginning
I've discussed the storage format that SQL Server uses to store datetime values, focusing on challenges related to the fact that there's no separation between date and time. But this is only the beginning. There are so many more challenges related to datetime manipulation. I'll continue exploring those challenges in the coming months.