How can I convert the time part of a given DATETIME
to the number of milliseconds since midnight? For instance,
'2011-08-29 00:00:00' -> 0
'2011-08-28 00:00:00' -> 0
'2011-08-29 01:00:00' -> 3600000
Is there any simple way to do this in SQL Server 2005?
WITH Dates(D) AS
(
SELECT { ts '2011-08-29 00:00:00.000' } UNION ALL
SELECT { ts '2011-08-28 00:00:00.000' } UNION ALL
SELECT { ts '2011-08-29 01:00:00.000' }
)
SELECT DATEDIFF(ms, 0,
DATEADD(Day, 0 - DATEDIFF(Day, 0, D), D))
FROM Dates