CODEWITHSUNDEEP
sql-serverssisrownum
Sachiko
Sachiko

Reputation: 924

DT_DBTIMESTAMP2 having only 3 digits

I'm having (DT_DBTIMESTAMP2,7)GETDATE() in SSIS Derived Column Transformation and Table column with datetime2(7). Even though I set 7 Digit Second Scale in both, but seems it comes only 3 digit. For example, I expected like '2018-05-02 16:45:15.6192346' but it comes '2018-05-02 16:45:15.6190000'.

The reason why I need the millseconds, I'd like to sort out the latest record from any duplications using timestamp. I realized only 3 digit second scale is not enough for this pourpose.

Except for Derived Column Transformation and Table Columns, is there any requrired setting in SSIS package? Any advices would be appreciated.

Upvotes: 1

Views: 5192

Answers (2)

Thom A
Thom A

Reputation: 95564

Although this is almost the same as what HoneyBadger has said, I'm expanding a little, as the OP isn't using the GETDATE() expression in SQL Server. The value 2018-05-02 16:45:15.619 could never be returned by GETDATE() (Transact-SQL) as it's only accurate to 1/300th of a second (thus the final digit can only every be 0,3, and 7 (technically 0, 333333333~ and 666666666~, which is why the final digit is a 7, as it's rounded up)).

In SSIS the GETDATE() expression returns a datatype of DB_TIMESTAMP. According to the Documentation:

A timestamp structure that consists of year, month, day, hour, minute, second, and fractional seconds. The fractional seconds have a maximum scale of 3 digits.

Thus, the last 4 characters are lost. Unfortunately, I don't believe there is a function in SSIS that returns the the current date and time to the accuracy you require. Thus, if you need this high level, you'll likely need to use an expression in SQL Server that does, such as SYSDATETIME() that HoneyBadger recommended.

Upvotes: 1

HoneyBadger
HoneyBadger

Reputation: 15140

GETDATE() returns a datetime, you should use SYSDATETIME() instead. See documentation.

edit

As noted by Larnu, you are probably using SSIS expression GETDATE, rather that the sql expression GETDATE as I assumed. The point is more-or-less the same though. GETDATE returns a DT_DBTIMESTAMP, where "The fractional seconds have a maximum scale of 3 digits." (Source).

Upvotes: 1

Related Questions