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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy