January 4, 2008 at 1:02 pm
hey guys......
can anyone by scripting show me how to increment a date timestamp column by a few milliseconds
thx
January 4, 2008 at 1:27 pm
If you are talking about the TIMESTAMP datatype, you can't do it.
From the BOL:
TIMESTAMP is a data type that exposes automatically generated, unique binary numbers within a database. timestamp is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The timestamp data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime data type.
Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a timestamp column within the database. This counter is the database timestamp. This tracks a relative time within a database, not an actual time that can be associated with a clock. A table can have only one timestamp column. Every time that a row with a timestamp column is modified or inserted, the incremented database timestamp value is inserted in the timestamp column.
-SQLBill
January 4, 2008 at 2:34 pm
with SQLBill's objection that timestamps have nothing to do with date or time anything in mind - SQL 2005 has a built-in function to return sequential timestamps in the default value of a column, called newsequentialID().
It will create "sequential" GUID's/timestamps.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 4, 2008 at 2:45 pm
on the other hand - if you're actually talking about a datetime field (i.e. a "date" field, not a timestamp field), then simply using something like
...dateadd(ms,3,mydatefield)...
will add 3 ms to a given datetime value. Please note that 3ms is the precision limit for a datetime field (i.e. datetimes are stored as a float with a 3ms precision essentially).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 4, 2008 at 2:47 pm
assuming your "date timestamp column" is really a datetime column that you're using to track when something happened:
dateadd( millisecond, 4, dateTimestampCol )
note that datetimes are only accurate to 3.33 ms so you'll need to add at least 4 ms to nudge it to a different value.
January 4, 2008 at 2:55 pm
Thanks for the additional info guys, I didn't know that the precision on a datetime as 3.33ms. I always just use a datetime column and pass in getdate(), so the dateadd(ms, 4, getdate()) should work perfect for what denby needs.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply