TheSQLGuru (5/8/2013)

---

Here is an interesting solution that could be more efficient (didn't test that):

select

DT,

CAST(dt AS decimal(29,15))*86400 AS decdtsec2,

SecondsSince_19000101 =

-- seconds for whole days

(datediff(dd,0,a.DT)*000000000086400)+

-- 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

I am curious about the occasional difference, but don't have time to investigate...