We are currently migrating from DB2 to SQL Server. DB2 supports TIMESTAMP(6-12) while DATETIME2 only supports up to 7 decimals. Since we have many keys represented as TIMESTAMP(12) what would be the best way of migrating these to SQL Server?

I was thinking we should store them as varchar(32) or char(32). This will allow storing all 12 fraction digits.

I assume order by ascending/descending will work the same with varchar/char as a datetime2.

I assume all SQL in the application can remain the same as datetime2 / timestamp values are represented as strings in SQL.

We will however have issues using time functions in SQL Server unless we cast back and forth (which probably won't perform well).

Any better suggestion?

  • Yes, I do need this resolution. There is a reason DB2 can support up to 12 digits and Oracle can support up to 9. Certain platforms (besides x86) do support this resolution in the clock and work fast enough to need this resolution. And because we are migrating a existing database using this precision we have to find a equivalent data type in MSSQL. Apr 6, 2017 at 7:34

2 Answers 2


Storing date/time data in a character-based column is a seriously bad idea. And, no, datetime2/timestamp values are not represented as strings in SQL Server. See Aaron Bertrand's excellent bad-habits blog series on the topic.

DB2's timestamp data type1 is:

a seven-part value representing a date and time by year, month, day, hour, minute, second, and microsecond, in the range of 0001-01-01- to 9999-12-31- with nanosecond precision. Timestamps can also hold timezone information

This equates nearly perfectly to a datetime2(7) data type in SQL Server2.

Time portion: 0 to 7 digits, with an accuracy of 100ns. The default precision is 7 digits.

Unless you are measuring exceptionally accurate particle accelerator physics, you likely don't need precision beyond 1 ns.

What time source are you using?

You mention in your question you are using these high-precision timestamp values as keys. I would investigate the appropriateness of storing those values in a numeric(32,12) column in SQL Server.

This is a sample table with that type of key column:

DROP TABLE dbo.TimeTest;
    ItemKey numeric(32,12) NOT NULL
        CONSTRAINT PK_TimeTest

Here, I insert a very large and precise value into the column:

INSERT INTO dbo.TimeTest (ItemKey)
VALUES (99999999999999999999.123456789012);

What that looks like:

FROM dbo.TimeTest;
|              ItemKey              |
| 99999999999999999999.123456789012 |

In order to successfully import your DB2 timestamp(12) values into SQL Server you'll need to export them as numeric values with the appropriate magnitude and precision. If you can do that, you won't lose any data, and will gain the ability for SQL Server to properly validate data used in the key columns. A numeric column is also extremely efficient for sorting, since it is not locale or collation sensitive.

Using a varchar(45) would allow keys to be created like this is a very long, non-numeric key; which would clearly be a bad idea.


Break up the timestamps into 2 columns: a datetime2(7) column that stores to the hundreds of nanoseconds and an int* column that captures the remainder.

|         DB2 timestamp(12)        |   SQL Server datetime2(7)   |  int  |
| 2017-04-08 | 2017-04-08 14:43:52.4560173 | 31416 |

Wherever you require a key with DB2-level precision, use a composite key with both columns.

When you don't need precision beyond 7 decimal places, you can leave the remainder column untouched. After all, adding microseconds to a timestamp will not change any digits beyond 6 decimal places. So, you can still use SQL Server's date/time functions on the datetime2 column. Beyond that, you're on your own.

Since you're migrating to SQL Server, I would hope that any new data or new business requirements would not exceed the precision that datetime2 can handle, and that you're only looking to accommodate data from your legacy system. As old data is archived, the need for the remainder column, and custom functions using it, should decrease over time.

*If you start building custom functions to replicate DATEADD/DATEDIFF functionality for increments/durations/TimeSpans smaller than 100 nanoseconds, add a constraint to keep the remainder column between 0 and 99999.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.