How to calculate the duration in seconds if a date is written in the following format: "2022-03-10T09:40:44.543Z"

Question

I have just imported in SQL an excel file which contains two columns ("Start Date" & "End Date") with the following format:

"2022-03-10T09:40:44.543Z".

I would like to calculate the difference expressed in second between "Start Date" and "End Date". Any suggestion please?

I have no idea how to proceed.

Answer 1

If you are using SQL Server you could try DATEDIFF

Syntax

DATEDIFF ( datepart , startdate , enddate )

In your case :

select DATEDIFF(Second,Start_Date,End_Date) as difference;

If you have stored the data on text columns you could use:

select DATEDIFF(Second,  CONVERT(varchar,Start_Date,127), CONVERT(varchar,End_Date,127) ) as difference;

Check demo for more details

Check the SQL Server date styles