SSIS expression builder: convert date/time to Epoc

Question

I'm trying to build an expression to convert a date/time parameter to an Epoch timestamp that will be used in a Url's parameter string.

I figured that I would try GetDate() first, then substitute the actual parameter (@[$Package::endingDate]).

This syntax:

DATEDIFF("s", "19700101", GETDATE() )

produces:

The function "DATEDIFF" does not support the data type "DT_WSTR" for parameter number 2. The type of the parameter could not be implicitly cast into a compatible type for the function. To perform this operation, the operand needs to be explicitly cast with a cast operator.

This syntax:

DATEDIFF("s", (DT_WSTR)"19700101", GETDATE() )

produces:

Attempt to parse the expression "DATEDIFF("s", (DT_WSTR)"19700101", GETDATE() )" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

This syntax:

DATEDIFF("s", CAST("19700101" AS DT_WSTR), GETDATE() )

produces:

Attempt to parse the expression "DATEDIFF("s", CAST("19700101" as DT_WSTR), GETDATE() )" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

Is there a way to convert a date/time to an Epoch timestamp, including the timezone adjustments?

Answer 1

Try this one :

DATEDIFF("SECOND",(DT_DBTIMESTAMP)"01/01/1970",GETDATE())