Converting Epoch time to Date Time

[wkopp]

Hello all, I'm trying to convert an Epoch time stamp to a standard date time and add that as a Private Data Key to the job. I am using a database call to send the Epoch value and convert it using a stored procedure in SQL Server. I get the correct date returned, but I am getting an 'unknown variable' error. I've tried changing the Database Variables from Date to Text but that doesn't see to have any affect. Any help would be appreciated!

Here's my statement in Switch:
[Database.Date:SQL="EXEC [DPI_ConvertEpochTime] [Job.PrivateData:Key="EpochTime"]",Connection="Tharstern",TimeZone="UTC"]

Sample value of "EpochTime" variable:
1675749785682

Here's my SP:
ALTER PROCEDURE [dbo].[DPI_ConvertEpochTime2] @Epoch VARCHAR
AS
DECLARE @EpochNumber BIGINT = cast(@Epoch as BIGINT)
DECLARE @DateTime DATETIME
SET @DateTime = (SELECT CAST(DATEADD(ms, CAST(RIGHT(@EpochNumber,3) AS SMALLINT),
DATEADD(s, @EpochNumber / 1000, '1970-01-01')) AS DATETIME2(3)))

SELECT @DateTime

[jan_suhr]

Have you tried the Date Calculator app?

https://www.enfocus.com/en/appstore/pro ... calculator

[wkopp]

Thanks for the tip jan_suhr. I use that app but didn't think about it for this.

I converted the seconds to days and used that number to add to 1970-01-01. Works like a charm!

Still would like to know what that error is all about though...

[mkayyyy]

It sounds like the variable error will be coming from the square brackets around your stored procedure name

Try changing the database variable to:

Code: Select all

[Database.Date:SQL="EXEC DPI_ConvertEpochTime [Job.PrivateData:Key="EpochTime"]",Connection="Tharstern",TimeZone="UTC"]

[wkopp]

It sounds like the variable error will be coming from the square brackets around your stored procedure name

Try changing the database variable to:

Code: Select all

[Database.Date:SQL="EXEC DPI_ConvertEpochTime [Job.PrivateData:Key="EpochTime"]",Connection="Tharstern",TimeZone="UTC"]

Thanks mkayyyy, that was it!