Jeffrey Williams 3188 (5/8/2013)
Michael Valentine Jones (5/8/2013)
select
DT,
SecondsSince_19000101 =
-- seconds for whole days as bigint
(datediff(dd,0,a.DT)*000000000086000)+
-- seconds since start of day
datediff(ss,dateadd(dd,datediff(dd,0,a.DT),0),a.DT)
from
( -- test data
select DT = getdate()union all
select DT = '99991231 23:59:59.997'union all
select DT = '18991231 12:01:01.997'union all
select DT = '17530101 00:00:00.000'
) a
order by
a.DT
Results:
DT SecondsSince_19000101
----------------------- ---------------------
1753-01-01 00:00:00.000 -4617340000
1899-12-31 12:01:01.997 -42739
2013-05-08 13:35:03.717 3560448903
9999-12-31 23:59:59.997 254427904399
Michael, can you explain why you used 86000 instead of 86400? There are 86,400 seconds in a day - and it appears your calculation will be off because of that.
Easy to explain. I just typed it wrong. :blush: