Re: Migration of Teradata and DB2 Timestamp fields.

From: Jacco Schalkwijk (NOSPAMjaccos_at_eurostop.co.uk)
Date: 05/19/04 

Date: Wed, 19 May 2004 15:25:41 +0100

There is no datatype in SQL Server that has a precision of 1 millisecond. I
can suggest 3 different workarounds:

- Store the timestamp in a CHAR(23) column in format
yyyy-mm-ddThh:mi:ss.nnn. You CONVERT this to and from datetime with style
126. Note that the precision will go back to 3.33 milliseconds when you
convert to datetime. This uses 23 bytes of storage.
- Store the timestamp as a UNIX timestamp (milliseconds since 1970-1-1) in a
BIGINT column. This uses 8 bytes of storage.
- Store the timestamp in 2 columns, one is a SMALLDATETIME (precision 1
minute), the second one is a INT, to store the number of milliseconds. This
uses 8 bytes of storage. (You can also use a SMALLINT if you want to save 2
bytes, but then you have to handle negative milliseconds.

Whichever is the most convenient of course depends on the calculations you
are going to do with it. 

-- 
Jacco Schalkwijk
SQL Server MVP
"Arumugam" <arumugam@optusnet.com.au> wrote in message
news:de3401c43bd6$5e60f870$a501280a@phx.gbl...
> I am trying to migrate timestamp fields in Teradata and
> DB2 to SQL server.  The problem is that Teradata and DB2
> store timestamps to an accuracy of 1 microsecond, while
> SQL Server stores it to the nearest 3.33 milliseconds
> (the datetime datatype).  The original accuracy needs to
> be preserved as the columns are part of the primary key.
>
> Is there an equivalent datatype in SQL Server or a quick
> workaround for this problem?  Date / Time functions need
> to be performed on these columns.

Relevant Pages

  • Re: Milliseconds to SQL Server DateTime
    ... in SQL Server using the ADO components. ... you can store milliseconds, but not exactly, because the ... it's because SQL-Server can't store a datetime ...
    (borland.public.delphi.database.ado)
  • Re: Access, MyODBC, MySQL Questions .....
    ... store the current date/time in the field. ... This is different than the SQL Server Timestamp field which automatically stores a binery representation of the current date/time that is not user usable. ...
    (comp.databases.ms-access)
  • Re: Convert java.util.Date to java.sql.Date
    ... Review my recommendation about java.text.DateFormat. ... Aside from its more natural match to the SQL TIMESTAMP type, Timestamp holds time to nanosecond resolution. ... Like its parent, java.util.Date, Timestamp holds long values that represent milliseconds and nanoseconds since epoch. ...
    (comp.lang.java.programmer)
  • Re: wait delay
    ... The datetime datatype in SQL Server is accurate to 3 milliseconds. ... Windows only updates the system clock every 10-15 milliseconds or so and SQL ... Server is probably using Win32 APIs to implement WAITFOR DELAY. ... You'll need to implement such a high resolution timer in client code rather ...
    (microsoft.public.sqlserver.programming)
  • Re: Date Calculations
    ... When you add 8 days to a given date/time the new date is still is eight days ... from the given date/time you started with, down to the milliseconds. ... or 30 days in a month mays no difference because SQL server store ... the datetime as the number of days and milliseconds since 1900-01-01 at ...
    (microsoft.public.sqlserver.programming)