Increased Datetime Precision

Any ideas on how to get greater than the 3.33 ms precision currently
available in the DataTime type?
Thanks,
Randy LRandy,
What are your requirements? What do you need a better precision for?|||What are you trying to do? DateTime has 1ms increments. DateTime.Now is
something like +- 50ms accurate.
William Stacey [MVP]
"Randy L." <randy.luchsinger@.community.nospam> wrote in message
news:uTZQiIXPGHA.1088@.tk2msftngp13.phx.gbl...
| Any ideas on how to get greater than the 3.33 ms precision currently
| available in the DataTime type?
|
| Thanks,
| Randy L
|
||||Store the milliseconds separately, in a SMALLINT column perhaps. To balance
the increased storage you could use SMALLDATETIME and then the number of
seconds and milliseconds separately.
"Randy L." <randy.luchsinger@.community.nospam> wrote in message
news:uTZQiIXPGHA.1088@.tk2msftngp13.phx.gbl...
> Any ideas on how to get greater than the 3.33 ms precision currently
> available in the DataTime type?
> Thanks,
> Randy L
>|||The precision you mention is the maximum precision of the datetime data
type.
If you need to store dates with times of greater precision in the time
portion, then you will have to program a solution yourself. One approach
is to use a smalldatetime (provided your dates fall within the range
supported by smalldatetime), and store the second and subsecond portion
of the timestamp in an int column as number of (remaining) milliseconds.
For example, to store the datetime 2006-03-01T22:19:03.999, you could
store it as:
CAST('2006-03-01T22:19:03.999' AS smalldatetime) AS
MostSignificantDateTimePart
,CAST(CAST(SUBSTRING('2006-03-01T22:19:03.999',18,5) AS decimal(5,3)) *
1000 AS int) AS LeastSignificantDateTimePart
HTH,
Gert-Jan
"Randy L." wrote:
> Any ideas on how to get greater than the 3.33 ms precision currently
> available in the DataTime type?
> Thanks,
> Randy L|||The transactions for which we are collecting this data occur very rapidly.
When we then store that time in SQL, the times are then truncated further.
As a result, ordering data by datetime does not always give the true order
of the transaction.
For example:
Trans# Actual Time Stored Time
-- -- --
--
1 2006-03-01 16:00:00.590014 2006-03-01 16:00:00.590
2 2006-03-01 16:00:00.590018 2006-03-01 16:00:00.590
3 2006-03-01 16:00:00.590030 2006-03-01 16:00:00.590
The result in SQL would be 3 times that are exactly the same.
Thanks,
Randy L
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1141247397.749764.161370@.u72g2000cwu.googlegroups.com...
> Randy,
> What are your requirements? What do you need a better precision for?
>|||Sorry, wrong ng. Thought I was on c# ng.
William Stacey [MVP]|||Randy,
just from the top of my head: I guess an identity column will give you
the true order of inserts...|||> just from the top of my head: I guess an identity column will give you
> the true order of inserts...
But not necessarily the true order of transaction date/time. The exact
moment in time may be coming from different systems, without synchronized
atomic precision (which is arguably more important in this case than in the
general case), and different system components might have lag times in which
the transactions are recorded (either because they are farther away or on
slower connections, or because they queue up their inserts, etc). Also, if
the table is populated via INSERT...SELECT...FROM there is no guarantee that
the IDENTITY values will be assigned in the 'expected' order.
A|||Alexander,
The transactions we track do not get inserted into SQL as they occur.
They occur across a variety of other processes and are then delivered to the
database. So the order by which they are inserted into the DB has no
significance.
Thanks,
Randy L
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1141248868.318368.227020@.i39g2000cwa.googlegroups.com...
> Randy,
> just from the top of my head: I guess an identity column will give you
> the true order of inserts...
>