# total time difference between more than two dates

• meenu.monu

Old Hand

Points: 349

advance thanks to all...can anybody helpme soon...........

i am using sql server 2005........i want to find out time difference between more than two dates in HH,MM,SS format.

and i want to find out total time difference of all time

eg

first i want the time difference of these two dates

02/Nov/06 9:14:21 AM

19/Apr/07 11:52:31 AM

second i want the time difference of these two dates

02/dec/07 12:14:21 AM

19/jan/08 5:52:31 AM

3rd

i want to find out the total time difference

now i am using this calculation in my procedure is as follows...but i think this is wrong...if we will seperatly calculate like this wrong i am getting

set @totaltravelHr=datediff(HH,@datediff1,@datediff2)

set @totaltravelMI=datediff(MI,@datediff1,@datediff2)

set @totaltravelSE=datediff(SS,@datediff1,@datediff2)

can anybody help me soon??????????

regards

• Jeff Moden

SSC Guru

Points: 995150

DateDiff and DateAdd have the tendency to round to whatever the "date part" is and, despite what many folks think, shouldn't be used to calculate what the difference between full dates and times are. Instead, simple subtraction should be used. For example...

`--===== Declare a couple of test variables to hold test data`

``` DECLARE @DateStart DATETIME DECLARE @DateEnd DATETIME --===== Preset the test variables with test data SELECT @DateStart = '02/Nov/06 9:14:21 AM', @DateEnd = '19/Apr/07 11:52:31 AM' --===== Calculate and display the difference of date/time between -- the two test variables SELECT @DateEnd-@DateStart AS [Difference between dates] Difference between dates ------------------------------------------------------ 1900-06-18 02:38:10.000 ```

`(1 row(s) affected)`

Of course, the problem with that is that we're displaying the difference using SQL Server's default format. It's hard for people to look at that and say "Hmmmm... that's 6 months, 18 days, 2 hours, 38 minutes, and 10 seconds difference. The code did the trick in calculating correctly, it's just not displayed in a manner easily understood by humans.

Teaching a man to fish... you're gonna have to trust me on this... Books On Line says that the underlying datatype for DATETIME is actually two integers that represent the number of days since 1900-01-01 00:00:00.000 AM (also known as "day zero"). DATETIMEs most correctly convert to FLOAT for math purposes. Just as in any type of math, half a day (12 noon, 1/2 of a day) would be represented by the decimal value of .5000000 and 6PM (3/4 of a day) would be represented by .75. So, in reality, the DATETIME datatype keeps track of the "Number of decimal fractional days". Midnight on 1900-01-02 would have the value of 1.000000 and noon on 1900-01-02 would have the value of 1.5...

... you with me, so far? Ok... back to the mathematical difference between dates and times...

Let's see what happens if we convert the difference between @DateStart and @DateEnd using some simpler date/times so we can follow along in our heads... and lets display the difference as the number of fractional days instead of letting SQL Server have it's way with us...

`--===== Declare a couple of test variables to hold test data`

``` DECLARE @DateStart DATETIME DECLARE @DateEnd DATETIME --===== Preset the test variables with test data SELECT @DateStart = '2000-01-01 00:00:00 AM', --Midnight @DateEnd = '2000-01-02 12:00:00 PM' --Noon the next day --===== Calculate and display the difference of date/time between -- the two test variables as a floating point number ```

` SELECT CAST(@DateEnd-@DateStart AS FLOAT)`

In our heads, we can see that if we start at midnight on one day and go to noon the next day, that's a day and a half or 1.5... kinda proves what I've been saying so far. How can we format that for human consumption?

I'm going to show you how, but you have to understand a couple of things...

1. This type of formatting should be done in the GUI... repeat after me... "THIS TYPE OF FORMATTING SHOULD BE DONE IN THE GUI". The reason why is because the GUI is better at it, allows for local date/time display settings to come into play, and keeps SQL Server from spending extra clock cycles for formatting (distributed computing... let the client spend the clock cycles instead of the server). If there is no GUI to do the formatting (ie, you're outputing to a file), then the formatting can be done in SQL Server.

2. Formatted date/times must never ever be stored in the database. Go ahead... get it into your head now... repeat it over and over. "FORMATTED DATE/TIMES MUST NEVER EVER BE STORED IN THE DATABASE." Stop and think about it... if we store the formatted differences in time in the database, we'll need to spend more clock cycles unformatting (changing back to DATETIME) to do any further calculations like adding up the individual differences to get a "total duration". It's like taking drugs... "Just say NO".

Promise yourself that you'll obey the two rules above... they will make you a much better SQL Developer than you'll know. They are two base rules to doing things right in any form of SQL.

Ok, back to your problems... Depending on what your requirements are, there are several ways to format a difference between dates/times (also known as "DeltaT" which is what I'll call it from now on). Let's assume that you want to display in the following format... ddddd DAYS(s) hh:mi:ss. In the example above, we'll need to display the whole portion of the number 1.5 as "1 DAY(s)"... and, we'll need to display the decimal portion as time as "12:00:00".

We'll do this in pieces and then put it all together...

For formatting the number of days, we need to take the following steps.

1. Calculate the DeltaT

2. Convert the DeltaT to FLOAT

3. Use only the whole day portion being careful to not round up

4. Convert the whole day portion to a character datatype so we can concatenate it to the word "DAY(s)". We also need to right justify the number so displays of other DeltaTs will line up for easy reading.

5. Concatenate the result with the word "DAY(S)"

Sounds really complicated, but it's simple, short, and can be done using a single formula...

`--===== Declare a couple of test variables to hold test data`

``` DECLARE @DateStart DATETIME DECLARE @DateEnd DATETIME --===== Preset the test variables with test data SELECT @DateStart = '2000-01-01 00:00:00 AM', --Midnight @DateEnd = '2000-01-02 12:00:00 PM' --Noon the next day --===== Calculate and display the difference in days ```

` SELECT STR(FLOOR(CAST(@DateEnd-@DateStart AS FLOAT)),5) + ' DAY(s)'`

Here's what the different pieces of code do... like any good math problem, we start from the inside parenthesis...

1. Calculate the DeltaT

@DateEnd-@DateStart

2. Convert the DeltaT to FLOAT (using CAST)

CAST(@DateEnd-@DateStart AS FLOAT)

3. Use only the whole day portion being careful to not round up (using FLOOR)

FLOOR(CAST(@DateEnd-@DateStart AS FLOAT))

4. Convert the whole day portion to a character datatype so we can concatenate it to the word "DAY(s)". We also need to right justify the number so displays of other DeltaTs will line up for easy reading. (using STR for 5 characters, does the right justify, too!)

STR(FLOOR(CAST(@DateEnd-@DateStart AS FLOAT)),5)

5. Concatenate the result with the word "DAY(S)"

STR(FLOOR(CAST(@DateEnd-@DateStart AS FLOAT)),5) + ' DAY(s) '

That wasn't so bad, was it?

Ok, now for the time portion... this is easy...

1. Calculate the DeltaT

2. Convert the DeltaT to FLOAT

3. Use only the decimal portion (ie. fraction day or TIME) being careful not to round

4. Convert the decimal portion to something that looks like a TIME and to a character datatype so we can concatenate it to days display we just calculated. We also need to right justify the time displays of other DeltaTs will line up for easy reading.

Again, this is simple, short, and can be done using a single formula...

`--===== Declare a couple of test variables to hold test data`

``` DECLARE @DateStart DATETIME DECLARE @DateEnd DATETIME --===== Preset the test variables with test data SELECT @DateStart = '2000-01-01 00:00:00 AM', --Midnight @DateEnd = '2000-01-02 12:00:00 PM' --Noon the next day --===== Calculate and display the TIME ```

` SELECT CONVERT(CHAR(8),@DateEnd-@DateStart,108)`

Here's what the different pieces of code do...

1. Calculate the DeltaT

@DateEnd-@DateStart

2. Convert the DeltaT to FLOAT

Step not actually needed because we're using the instrinsic date functions of SQL Server to do this.

3. Use only the decimal portion (ie. fraction day or TIME) being careful not to round

Again, this step not actually needed because we're using the instrinsic date functions of SQL Server to do this.

4. Convert the decimal portion to something that looks like a TIME and to a character datatype so we can concatenate it to days display we just calculated. We also need to right justify the time displays of other DeltaTs will line up for easy reading. (using CONVERT)

CONVERT(CHAR(8),@DateEnd-@DateStart,108)

Heh... guess step 4 takes a bit more explanation... CONVERT is the command to do steps 2, 3, 4. The CHAR(8) is the data type that we want the result to be (8 characters to hold the 8 character format of hh:mi:ss). The 108 is the format number I got from Books Online (lookup CONVERT) that will give me the hh:mi:ss format in 24 hour time. Since the 108 format only deals with the time portion of the DeltaT, theres no need to convert to FLOAT and no need to strip of the number of whole days.

Ok... now, using the lessons we just learned, put it all together...

`--===== Declare a couple of test variables to hold test data`

``` DECLARE @DateStart DATETIME DECLARE @DateEnd DATETIME --===== Preset the test variables with test data SELECT @DateStart = '2000-01-01 00:00:00 AM', --Midnight @DateEnd = '2000-01-02 12:00:00 PM' --Noon the next day --===== Calculate and display the DeltaT in the (ddddd DAY(s) hh:mi:ss) format SELECT STR(FLOOR(CAST(@DateEnd-@DateStart AS FLOAT)),5) + ' DAY(s) ' ```

` + CONVERT(CHAR(8),@DateEnd-@DateStart,108)`

You can just change the values for @DateStart and @DateEnd and your first two problems are solved. Pretty easy, huh?

Again, don't even think about storing that garbage result in a perfectly good database... this type of stuff (formatting) is for display purposes only.

The same method will work for your third problem... and I'm going to let you do it because I think this is homework and you need to try it on your own. I will give you a couple of hints though...

1. Calculate two DeltaT's... 1 for problem 1 and 1 for problem 2

2. Add the two DeltaT's together

3. Format the result

I know this is a bit long winded, but I saw the opportunity to teach someone something instead of just plopping a code answer out there. You have a bit of study to do to figure out what the individual functions like STR and CONVERT are actually capable of. And, if you have any questions that whose answers aren't in Books Online, feel free to post back.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

How to post code problems
Create a Tally Function (fnTally)

• meenu.monu

Old Hand

Points: 349

dear all

thanks for ur help....

i done this caculation...

http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server

• Jeff Moden

SSC Guru

Points: 995150

Cool... if that's what you actually wanted.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

How to post code problems
Create a Tally Function (fnTally)

• RBarryYoung

SSC Guru

Points: 143327

Jeff: I think that's an article.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc.
[/font]
[font="Verdana"] "Performance is our middle name."[/font]

• Tom Garth

SSCertifiable

Points: 6173

rbarryyoung (4/13/2008)

Jeff: I think that's an article.

It is an article, and very well written. No editing required. Nice work Jeff.

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
• GSquared

SSC Guru

Points: 260824

Jeff, good article. Just to add to it, you can do this:

``` declare @D1 datetime, @D2 datetime select @d1 = '02/Nov/06 9:14:21 AM', @d2 = '19/Apr/07 11:52:31 AM' select datepart(year, @d2-@d1)-1900 as years, datepart(month, @d2-@d1) as months, datepart(day, @d2-@d1) as days, datepart(hour, @d2-@d1) as hours, datepart(minute, @d2-@d1) as minutes, datepart(second, @d2-@d1) as seconds ```

And it will give you a result of how many years, months, days, hours, minutes, seconds, are between the two dates provided.

Get rid of the "declare...select" part and make those input parameters, and you have a function. Change the variables to columns and you have a set-based function.

If you need it, you could also add milliseconds to the final select.

The advantage to this one is that it can be used to create calculated columns that can be selected against in a view, indexed, etc., as needed. Doesn't do the string formatting that Jeff's solution does, which is an advantage if you need this for a Where clause, and a very minor disadvantage if you want formatted data.

The years part of the calculation can also be used to find age.

- 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

• Jeff Moden

SSC Guru

Points: 995150

Thanks, guys... I appreciate the feedback...

Gus, did you test for rounding errors?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

How to post code problems
Create a Tally Function (fnTally)

• GSquared

SSC Guru

Points: 260824

Well, since dates are stored as floating point numbers, it can have those problems, but there isn't a way around that if you're using datetime data. And it should only have errors in the milliseconds measure.

Is there some specific situation you were thinking of? The things I've tested it on seem to be correct. I haven't use this, just thought it was an interesting calculation and might work here.

Where it can have problems is if you input the dates backwards and subtract a later date from an earlier date. If you can't protect against that, then it's important to use ABS on the date subtraction.

It does have some intersting issues, now that I test it a bit more. For example, I used 1 Jan 1800 and 1 Jan 1901 as the dates, and it came back as 100 years, 12 months, 31 days, 0 hours, 0 minutes, 0 seconds, 0 milliseconds.

If I modify it to have -1 on the month and day, it gives accurate counts. Hadn't taken into account that Jan != 0. With that correction, it fixes the 100 years, 12 months, 31 days, problem.

With that correction, I just tested it on a few dozen dates, and it was right in each case.

- 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

• Jeff Moden

SSC Guru

Points: 995150

GSquared (4/14/2008)

Jeff, good article. Just to add to it, you can do this:

``` declare @D1 datetime, @D2 datetime select @d1 = '02/Nov/06 9:14:21 AM', @d2 = '19/Apr/07 11:52:31 AM' select datepart(year, @d2-@d1)-1900 as years, datepart(month, @d2-@d1) as months, datepart(day, @d2-@d1) as days, datepart(hour, @d2-@d1) as hours, datepart(minute, @d2-@d1) as minutes, datepart(second, @d2-@d1) as seconds ```

And it will give you a result of how many years, months, days, hours, minutes, seconds, are between the two dates provided.

Get rid of the "declare...select" part and make those input parameters, and you have a function. Change the variables to columns and you have a set-based function.

If you need it, you could also add milliseconds to the final select.

The advantage to this one is that it can be used to create calculated columns that can be selected against in a view, indexed, etc., as needed. Doesn't do the string formatting that Jeff's solution does, which is an advantage if you need this for a Where clause, and a very minor disadvantage if you want formatted data.

The years part of the calculation can also be used to find age.

I think it needs just a little tweek, Gus... that's what I meant by a "rounding error" although it's not really a "rounding" error...

`declare @D1 datetime, @D2 datetime`

``` select @d1 = '02/Nov/06 00:00:00AM', @d2 = '02/Nov/06 12:00:00PM' select datepart(year, @d2-@d1)-1900 as years, datepart(month, @d2-@d1) as months, datepart(day, @d2-@d1) as days, datepart(hour, @d2-@d1) as hours, datepart(minute, @d2-@d1) as minutes, datepart(second, @d2-@d1) as seconds years months days hours minutes seconds ----------- ----------- ----------- ----------- ----------- ----------- 0 1 1 12 0 0 (1 row(s) affected) ```

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

How to post code problems
Create a Tally Function (fnTally)

• Jeff Moden

SSC Guru

Points: 995150

Sorry, Gus... we're posting in parallel...

The -1 addition will work but I think the tweek needs to be extended to the days column, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

How to post code problems
Create a Tally Function (fnTally)

• GSquared

SSC Guru

Points: 260824

Yeah. It's not precisely a rounding error. It's a "1 Jan" does not equal 0 error. Needs -1 on the month and day columns because of that.

- 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

• GSquared

SSC Guru

Points: 260824

Only month and day need the -1. 0 = midnight, so hours, minutes, seconds, etc., don't need that. Years already has a -1900 (because that's 0 in datetime).

Try it with that.

- 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

• Jeff Moden

SSC Guru

Points: 995150

Heh... you started it... was hoping you had some final code 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

How to post code problems
Create a Tally Function (fnTally)

• GSquared

SSC Guru

Points: 260824

Modified code:

``` declare @D1 datetime, @D2 datetime select @d1 = '1/30/2000 11:59:59 pm', @d2 = '1/31/2000 12:00:01 am' select datepart(year, @d2-@d1)-1900 as years, datepart(month, @d2-@d1)-1 as months, datepart(day, @d2-@d1)-1 as days, datepart(hour, @d2-@d1) as hours, datepart(minute, @d2-@d1) as minutes, datepart(second, @d2-@d1) as seconds, datepart(millisecond, @d2-@d1) as milliseconds ```

Tried it with:

1 Jan 2000 11:59:59 PM and 2 Jan 2000 12:00:01 AM

1 Jan 1800 and 1 Jan 1900

28 Feb 2008 and 1 Mar 2008 -- To test for leap years

12 Dec 2007 and 12 Dec 2007 -- To test for 0

12 Dec 2007 and 12 Dec 2008

Stopped there. The first 4 worked perfectly. The last one gave back 1 year and 1 day. It's because of the leap year, since the Date2-Date1 float calculation gives 2 Jan 1901. Makes the whole thing not work without various Case statements.

- 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

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply