In SQL Server, calculate the difference between two time strings
- First convert these strings to date/time types (such as datetime or datetime2)
- Then use the date function to calculate the difference between them.
1. Calculate the difference between two time strings
Case: Calculate the time difference in seconds between two time points, starttime and endtime.
The following data is found in the test table:
| starttime | endtime |
|---|---|
| 2024-10-12 10:00:00 | 2024-10-12 10:30:00 |
| 2024-10-12 11:00:00 | 2024-10-12 11:45:00 |
Here are the complete SQL statements:
SELECT DATEDIFF(SECOND, CAST(starttime AS DATETIME), CAST(endtime AS DATETIME)) AS "Time Difference" FROM test;
explain
- CAST(starttime AS DATETIME): Converts the starttime string to the DATETIME type.
- CAST(endtime AS DATETIME): Converts the endtime string to the DATETIME type.
- DATEDIFF(SECOND, …): Calculate the difference in seconds between two DATETIME values.
After executing the above query, the result will be:
| Time difference |
|---|
| 1800 |
| 2700 |
2. Convert string to date/time type
Assume that starttime is a VARCHAR type column that stores a string shaped like ‘2024-10-12 10:00:00’
SELECT CAST(starttime AS DATETIME) AS StartTimeAsDatetime FROM test;
test is the name of the table containing the starttime column. The query returns a new column, StartTimeAsDatetime, which contains the converted DATETIME value.
- If the format of starttime is ‘yyyy-MM-dd HH:mm:ss’, then it can be converted directly.
- If the starttime is incorrectly formatted, CAST fails with an error thrown. In this case, you may need to use the CONVERT function and specify the style code to correctly parse the datetime string.
For example, if the starttime format is a string of 'MM/dd/yyy HH:mm:ss' , '10/12/2024 12:09:36'
SELECT CONVERT(DATETIME, starttime, 2) AS StartTimeAsDatetime FROM test;
2 is the style code, indicating that the input date and time string format is 'MM/dd/yyyy HH:mm:ss'
Style code 2 actually corresponds to the date in the '' format, rather than the full date and time containing the time part.
If your starttime string contains the time part and the format is 'MM/dd/yyyy HH:mm:ss', you should use style code 102 or other appropriate style code.
Here are some common datetime style codes:
| Style code | Date format |
|---|---|
| 102 | mm/dd/yyyy |
| 103 | dd/mm/yyyy |
| 104 | |
| 105 | dd-mm-yyyy |
| 106 | dd mon yyyy |
| 107 | mon dd, yyyy |
| 108 | hh:mi:ss |
| 109 | mon dd yyyy hh:mi:ss:mmmAM (or PM) |
| 110 | mm-dd-yyyy |
| 111 | yyyy/mm/dd |
| 112 | yyyymmdd |
| 113 | dd mmm yyyy hh:mi:ss:mmm(24h) |
Assuming your starttime string format is ‘MM/dd/yyyy HH:mm:ss’, you can use CONVERT like this:
SELECT CONVERT(DATETIME, starttime, 102) AS StartTimeAsDatetime FROM test;
3. Calculate the difference between two dates and times
Case:
Calculate the difference in the number of days between the startdate and enddate dates.
The following data is found in the test table:
| startdate | enddate |
|---|---|
| 2024-10-12 | 2024-10-15 |
| 2024-10-18 | 2024-10-22 |
SELECT DATEDIFF(DAY, startdate, enddate) AS "Difference in days" FROM test;
- DATEDIFF(DAY, startdate, enddate): calculates the difference in the number of days between startdate and enddate.
- AS "Day Difference": Assign an alias to the result column to make it more readable.
After executing the above query, the result will be:
| The difference in the number of days |
|---|
| 3 |
| 4 |
template
select DATEDIFF(unit, startdate, enddate) from test
- unit is a time unit, which can be SECOND, MINUTE, HOUR, DAY, MONTH, YEAR, etc.
- startdate and enddate are the two date/time values you want to compare.
- The second date is earlier than the first date and the result will be a negative number
This is the article about the example code for SQL Server calculating two time differences. For more information about SQL Server calculating time differences, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!