April 15, 2010 at 6:35 am
Hi,
I have only been using SQL for a short time and hoping for some help/guidance.
I have a dataset which has two time fields and I am looking to calculate the difference between the two times. The problem that I am having is that the dataset has been set up with these fields as nvarchar(50). The field displays the times in this field as hh:mm:ss (11:11:11).
Some google searches have led me to believe that I can convert these fields and perform the calculation. Is this correct?
I am hoping to output the difference between the times as hh:mm:ss, counting the number of each value.
Any help is appreciated.
Bobuh
April 15, 2010 at 6:47 am
You can use DateDiff to get that.
Here's a sample. Doesn't do the whole job, but should get you started:
CREATE TABLE #T (
ID INT IDENTITY PRIMARY KEY,
T1 NVARCHAR(50),
T2 NVARCHAR(50));
INSERT INTO #T (T1, T2)
VALUES ('12:00:00','13:00:00'),('12:00:00','15:37:05');
SELECT
DATEDIFF(hour, CAST(T1 AS DATETIME), CAST(T2 AS DATETIME)),
datediff(minute, CAST(T1 AS DATETIME), CAST(T2 AS DATETIME)) - 60 * DATEDIFF(hour, CAST(T1 AS DATETIME), CAST(T2 AS DATETIME)),
DATEDIFF(second, CAST(T1 AS DATETIME), CAST(T2 AS DATETIME)) - 60 * (datediff(minute, CAST(T1 AS DATETIME), CAST(T2 AS DATETIME)) - 60 * DATEDIFF(hour, CAST(T1 AS DATETIME), CAST(T2 AS DATETIME))) - 3600 * DATEDIFF(hour, CAST(T1 AS DATETIME), CAST(T2 AS DATETIME))
FROM #T;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2010 at 7:07 am
Thank you for such a promt response.
Using the datediff functions suggested I have managed to get some data out, however, some of the results are a little unexpected with negative values within the minutes and seconds columns.
An example of the data I am returning is below.
HourMinutesSecondsTotal
0402
0701
01007
1-35-601
120-6011
2-44-1202
2-41-1202
2-35-1201
2-5-921
240-1204
3-50-1807
Have I done something wrong when implementing the datediff function?
Bobuh
April 15, 2010 at 7:21 am
I have had a quick look at the raw data and the negative valuse are occurring where the minutes or seconds value for the second time is lower than that of the first time. Examples below.
Example 1.
Time 1 Time 2
12:57:00 13:00:00
returns
Hour Minutes Seconds
1 -57 -60
ideally this should return
0 3 0
Example 2.
Time 1 Time 2
00:00:00 23:59:59
returns
Hour Minutes Seconds
23 59 -1380
ideally this should return
23 59 59
Bobuh
April 15, 2010 at 7:34 am
Try using DateDiff(second, T1, T2), then divide by 3600 to get hours, then divide the modulus of that by 60 to get minutes, then use the modulus to get seconds.
My example was simply to show options for DateDiff.
SELECT
DATEDIFF(second, CAST(T1 AS DATETIME), CAST(T2 AS DATETIME))/3600 AS [Hours],
DATEDIFF(second, CAST(T1 AS DATETIME), CAST(T2 AS DATETIME))%3600/60 AS [Minutes],
DATEDIFF(second, CAST(T1 AS DATETIME), CAST(T2 AS DATETIME))%3600%60 AS [Seconds]
FROM #T;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 19, 2010 at 2:23 am
Thanks for the responses. After trying a few things out this morning, I have found that the following works.
select convert (varchar(30),cast (Time2 as datetime) - cast (Time1 as datetime),108) as 'Duration'
Thanks again for your help and time.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy