CODEWITHSUNDEEP
sql-serverssisexpressionetlderived-column
Gargoyle
Gargoyle

Reputation: 10294

Convert numeric(32,0) to DATETIME in SSIS job

My data source is giving me a column of type numeric(32,0) that is the epoch value, and I want to convert that to a DATETIME in my database. I've got a Derived Column transform and for the expression I tried to do this:

DATEADD("SECOND", CAST([Last Modified Date] AS BIGINT) / 1000, (DT_DBTIMESTAMP)"1970-01-01")

But it's giving me parsing errors. Can someone help me with the proper syntax for this please? If I don't quote "SECOND" then it immediately tells me that SECOND is not an input column.

Even though the input is the numeric(32,0) type they're all integer values, such as 1564371486110.

Upvotes: 1

Views: 214

Answers (1)

TEEKAY
TEEKAY

Reputation: 1186

I do not immediately understand your cast and divide by 1000.

SSMS

select datediff(ss,  '1970-01-01 00:00:00', getdate())
1564069434

Expression

dateadd("SS", 1564069434, (DT_DBTIMESTAMP)"1970-01-01 00:00:00")
2019-07-25 15:51:17.763

Edit - I am going to assume you're dividing for milliseconds on the epoch time.

Derived Column Transformation Editor - Expression

 DATEADD("SS",(DT_UI8)SUBSTRING((DT_WSTR,32)epochtime,1,LEN(TRIM((DT_WSTR,32)epochtime)) - 3),(DT_DBTIMESTAMP)"1970-01-01 00:00:00")

Upvotes: 1

Related Questions