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.
End of Article
Prev. page
1
[2]
next page -->
