Submitted by
Category
Review Cycle
.
Public
Joachim Mutter/sysarc
on
11/05/2008
at
04:28 PM
SQLServer\Documentation
SQL Datetime and milliseconds
Duration
select
convert
(
numeric
(
8
,
2
),
(
10.0
/
3000.0
)
*
(
convert
(
int
,
convert
(
binary
(
4
),
b
.
[ExecStopDT]
))
-
convert
(
int
,
convert
(
binary
(
4
),
b
.
[ExecStartDT]
))))
[Seconds]
,
convert
(
varchar
(
12
),
convert
(
datetime
,
convert
(
binary
(
4
),
[ExecStopDT]
-
[ExecStartDT]
)),
14
)
[Duration]
,
from
AuditTableProcessing
DateOnly
select
floor
(
convert
(
float
,
convert
(
datetime
,
startdate
)))
from ImError
TimeOnly
select
Convert
(
DATETIME
,
Convert
(
binary
(8),
convert
(
int
,
substring
(
convert
(
binary
(
8
),
startdate
),
5
,
4
)))) [Starttime]
(
10.0
/
3000.0
)
*
(
convert
(
int
,
convert
(
binary
(
4
),
startdate
)) [Seconds since midnight]
from ImError
To use a buildin function which returns the differnece of two datetime values as seconds
(This code woks also as a Comuted column formula, where convert to binary is a non deterministic function and therefor doesnt work!)
select
datediff
(
second
,
[StartDate]
,
[EndDate]
))
[Duration]
from ImError
SQL Server uses 8 bytes to store the datetime data type.
The first 4 bytes make up an integer value that represents the number of days since January 1, 1900.
The second 4 bytes are an integer value that represents the number of 3.33 ms intervals since midnight.
To compute this without any rounding problems, use 10/3 instead of 3.33 !!!
=> Computation of seconds from a datetime value could be done very easily with that information
select (10/3 * (convert(int, substring(convert(binary(8), getdate()), 5, 4) ))) / 1000.0 As Seconds
select (10/3 * (convert(int, substring(convert(binary(8), getdate()), 5, 4) ))) / 3600000.0 As hours
=> Computation of datetime value could be done very easily if you have a running number from 0-86400 (24h * 60 min * 60 sec)
(For a computation of sequence numbers see "Creating Sequence Numbers" [
] )
CONVERT
(
dateTime
,
convert
(
binary
(
8
),
CONVERT
(
INT
,
(
N
-
1
)
*
300
)))
This is seconds[0 ... 86399] * (10/3 * 1000) = milliseconds (as 3.33 Intervall) and converted to binary(8)
will set the 4 LSB bytes, which are the time part of a datetime variable.
Then we convert this binary(8) to datetime and voila, we had a valid datetime (without the datepart)
Datetime vs smalldatetime:
8 byte size: Datetime timespan a year span of 1753 to 9999 and a resolution of 1/300 of seconds (0.00333 sec).
4 Byte size: Where as smalldatetime timespan a year sapn of 1900 to 2079 with a presition of 1sec.
Zur SysArc Homepage ...
