Calculated column using DATEDIFF ()
I have a TABLE showing an entire month - Ticket Open Start Date like this ( 2015-07-28 06:40:52:337
) and Ticket Close End Date like this ( 2015-07-29 07:45:52:337
).
Using the T-SQL DATEDIFF()
I want to find the difference in the DAYS
, HOURS
, MINUTES
and SECS
and put it into a new computed column named TimeDiff
.
How can i do this?
source to share
Using DATEDIFF
SELECT CAST(DATEDIFF(DAY, [Opened Start Date], [Close End Date]) AS VARCHAR(5)) + ' Day(s), ' +
CAST(DATEDIFF(HOUR, [Opened Start Date], [Close End Date]) AS VARCHAR(5)) + ' Hour(s), ' +
CAST(DATEDIFF(MINUTE, [Opened Start Date], [Close End Date]) AS VARCHAR(5)) + ' Minute(s), ' +
CAST(DATEDIFF(SECOND, [Opened Start Date], [Close End Date]) AS VARCHAR(5)) + ' Second(s), ' AS TimeDiff
FROM yourTable
source to share
You probably need something like this:
CREATE TABLE Table1
(
[starttime] datetime, [endtime] datetime,
TimeDiff as
convert(varchar(3), (DATEDIFF(SECOND, [starttime], [endtime])/86400)) + ' Days, ' +
convert(varchar(2), ((DATEDIFF(SECOND, [starttime], [endtime])%86400)/3600)) + ' Hours, ' +
convert(varchar(2), (((DATEDIFF(SECOND, [starttime], [endtime])%86400)%3600)/60)) + ' Minutes, ' +
convert(varchar(2), (((DATEDIFF(SECOND, [starttime], [endtime])%86400)%3600)%60)) + ' Seconds',
);
This calculates all the differences in seconds and calculates each of the time portions from there.
Example in SQL Fiddle
source to share