CODEWITHSUNDEEP
sql-serverssisdata-transform
Dont_know
Dont_know

Reputation: 13

Trying to convert time format 0:00:00 to seconds (integer)

Trying to convert time format 0:00:00 to seconds (integer), to use as a derive column in SSIS. I've tried (DT_I4)TOKEN(column,":",1) * 60 + (DT_I4)TOKEN(column,":",2), but it is not recognizing the hour portion whether it has a leading zero or not

Upvotes: 0

Views: 43

Answers (1)

Ozan Sen
Ozan Sen

Reputation: 2615

You can achieve this using simple query logic in SQL Server,

SELECT
    (TMP.Hour_Part * 3600 + TMP.Minute_Part * 60 + TMP.Second_Part * 1) AS Total_Time_In_Seconds
FROM(
SELECT 
    '03:50:00' AS Curr_Time,
    DATEPART(HOUR,'03:50:00') AS Hour_Part,
    DATEPART(MINUTE,'03:50:00') AS Minute_Part,
    DATEPART(SECOND,'03:50:00') AS Second_Part
) AS TMP

Result:

Image_Result

You will use the same function in SSIS also to get similar results, just the first parameter surrounded by double quotation marks:

https://learn.microsoft.com/en-us/sql/integration-services/expressions/datepart-ssis-expression?view=sql-server-ver16

GERG

Upvotes: 0

Related Questions