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

Incorrect date in column for 25,000 rows

I have a table with a column in it called Date, which is of the type DateTime, and for the last two years I have been adding data which I found out was incorrect.

My dates are all a day in the future, so I need to reduce each date by one day.

I can easily use a select script to reveal the 25,000 rows which are all incorrect dates. But I can't figure out how to update each and every row to subtract one day from each date.

So where I have:

26/01/2005

I would like to have:

25/01/2005

and of course for every record. Obviously way too many to do manually :-(

Can anyone show me a script that will get what I'm after.

Tia

Tailwag

After one your 25000 rows with invalid data, that is not nice to find out. But it is easy to solve, just run this query:

UPDATE [MyTable] SET [MyDate] = DATEADD(year, -1, [MyDate])

Don't forget to replace MyTable with your table name and MyDate with your datetime field name.
I have tested it before posting this solution, because i don't want to be responsible for lozing 25000 rows on friday.

|||

Also, replace year with day!

So the update query must be:

UPDATE [MyTable] SET [MyDate] = DATEADD(day, -1, [MyDate])

- Jeroen Boiten

|||Indeed! Thanks Jeroen, i overlooked that one. Thought only the years where incorrect.|||

Thank you PJ, and also Jeroen. I ran the query and 'Viola' it worked flawlessly, you guys are now extremely high on my best friends of all time list

Tia.

Tailwag

P.S. The B_ _ _ _ Friday curse has finally been thwarted!!!