How to calculate the difference in seconds between 2 datetime strings (yyyyMMdd HHmmss) in a SSIS expression

If you want to calculate the difference in seconds between 2 datetime strings (yyyyMMdd HHmmss) in a SSIS expression, you can use the following expression:

 

Pre

@[User::StartDateTimeString] = “20100420 100000”

@[User::EndDateTimeString] = “20100420 101010”

 

Expression

DATEDIFF("ss",(DT_DBTIMESTAMP)(SUBSTRING(@[User::StartDateTimeString],5,2) + "/" + SUBSTRING(@[User::StartDateTimeString],7,2) + "/" + SUBSTRING(@[User::StartDateTimeString],1,4) + " " + SUBSTRING(@[User::StartDateTimeString],10,2) + ":" + SUBSTRING(@[User::StartDateTimeString],12,2) + ":" + SUBSTRING(@[User::StartDateTimeString],14,2)),(DT_DBTIMESTAMP)(SUBSTRING(@[User::EndDateTimeString],5,2) + "/" + SUBSTRING(@[User::EndDateTimeString],7,2) + "/" + SUBSTRING(@[User::EndDateTimeString],1,4) + " " + SUBSTRING(@[User::EndDateTimeString],10,2) + ":" + SUBSTRING(@[User::EndDateTimeString],12,2) + ":" + SUBSTRING(@[User::EndDateTimeString],14,2)))

 

Result

610

 

 

image

Leave a Reply

Your email address will not be published. Required fields are marked *