asked on

How do I ADD milliseconds to a DATE/TIME FIELD?

Experts:

I have a TIME field that looks like this:

1899-12-30 23:31:09.000

I have a DURATION field that is stored in MILLISECONSD and looks like this:

7209

What is the "syntax" to use to ADD the MILLISECONDS to the TIME field in order to obtain the exact time after the DURATION of the milliseconds?

Thanks
M

ASKER CERTIFIED SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Marcus Aurelius

ASKER

Interesting. So this is what it is giving me:

Here is my TimeField-
1899-12-30 21:33:02.000

And here is my Duration Field-
55

When I use this in a column:

, dateadd(ms, prequeue, start_time) as [StartTime + PreQueue]

I get this:

1899-12-30 21:33:02.057

Just curious, why does it add 2 milliseconds to the calc....???

Marcus Aurelius

ASKER

Not all do this but a great majority do add the few milliseconds to the calc...???

BrandonGalderisi

SQL Server's datetime data type is not infinitely granular.

select convert(datetime, '1899-12-30 21:33:02.055', 21)

Yields:

'1899-12-30 21:33:02.057'

Marcus Aurelius

ASKER

Ok...thanks for the great info....

dportas

If you require accuracy of more than about 3 milliseconds then you could upgrade to SQL Server 2008 which supports datetime accuracy to 100 nanoseconds.

If that's not possible then you'll have to use a non-datetime type in 2005. You could use a SMALLINT for the milliseconds.