|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Time Difference CalculationsI am trying to calculate the time difference between two cells,
However I have tried the datediff but it does not display the answer that i require, I am looking for the time diffence to be displayed in months, days, hours, mins and seconds, Can anyone give any help? Also, Is there any way to change the time format that is display when you select the time from the cells, ie, it is sotred in US format but I would like it to be displayed in UK format, thanks for any help datetime data is not stored internally in SQL Server in US format or UK
format. It's not a string but some floating point data. How it is displayed should be handled at the client, but if you really want to force the returned data to some particular string representation you can use the CONVERT() <http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp> function with the /style/ parameter. For example, CONVERT(varchar(10),@mydatetime,103) or CONVERT(varchar(30),@mydatetime,131) Also, since the 2 datetime values are both essentially floating point you can simply do floating point arithmetic (subtraction in this case) to get your answer. Like this: declare @d1 datetime declare @d2 datetime declare @d3 datetime select @d1 = '20050101' select @d2 = getdate() select @d3 = @d2 - @d1 select datepart(mm,@d3) as Months, datepart(dd,@d3) as Days, datepart(hh,@d3) as Hours, datepart(mi,@d3) as Minutes, datepart(ss,@d3) as Seconds Hope this helps. Show quote >I am trying to calculate the time difference between two cells, >However I have tried the datediff but it does not display the answer that i >require, >I am looking for the time diffence to be displayed in months, days, hours, >mins and seconds, > >Can anyone give any help? > >Also, Is there any way to change the time format that is display when you >select the time from the cells, ie, it is sotred in US format but I would >like it to be displayed in UK format, > >thanks for any help > > thanks mike, the first part done exactly what i wanted but cant get the
second part to work, it keeps displaying a month and days time difference even when tere isnt and i require to put a select statement within the 2nd variable Show quote "Mike Hodgson" wrote: > datetime data is not stored internally in SQL Server in US format or UK > format. It's not a string but some floating point data. How it is > displayed should be handled at the client, but if you really want to > force the returned data to some particular string representation you can > use the CONVERT() > <http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp> > function with the /style/ parameter. For example, > > CONVERT(varchar(10),@mydatetime,103) > > or > > CONVERT(varchar(30),@mydatetime,131) > > Also, since the 2 datetime values are both essentially floating point > you can simply do floating point arithmetic (subtraction in this case) > to get your answer. Like this: > > declare @d1 datetime > declare @d2 datetime > declare @d3 datetime > > select @d1 = '20050101' > select @d2 = getdate() > > select @d3 = @d2 - @d1 > > select > datepart(mm,@d3) as Months, > datepart(dd,@d3) as Days, > datepart(hh,@d3) as Hours, > datepart(mi,@d3) as Minutes, > datepart(ss,@d3) as Seconds > > Hope this helps. > > -- > *mike hodgson* > blog: http://sqlnerd.blogspot.com > > > > Trigger wrote: > > >I am trying to calculate the time difference between two cells, > >However I have tried the datediff but it does not display the answer that i > >require, > >I am looking for the time diffence to be displayed in months, days, hours, > >mins and seconds, > > > >Can anyone give any help? > > > >Also, Is there any way to change the time format that is display when you > >select the time from the cells, ie, it is sotred in US format but I would > >like it to be displayed in UK format, > > > >thanks for any help > > > > > Yeah, I can see that now that I think about it a little more. The time
part is easy. You can also do it like: declare @d1 datetime declare @d2 datetime select @d1 = '20050731' select @d2 = getdate() select datediff(hh,@d1,@d2) % 24 as Hours, datediff(mi,@d1,@d2) % 60 as Minutes, datediff(ss,@d1,@d2) % 60 as Seconds But the days & months are tricky primarily because there are a variable number of days in each month. For example, is the difference between '2005-02-28' and '2005-03-30' 0 months & 30 days or is it 1 month & 2 days? You have to define how you wish to deal with the days (i.e. business rule) before you can define the logic to derive that. (Plus I'm too tired at the moment (9pm Friday night) to figure that puzzle out. Itzik Ben-Gan has the kind of brain that can figure that kind of problem out in a heartbeat; but not me I'm afraid, at least not tonight.) Show quote >thanks mike, the first part done exactly what i wanted but cant get the >second part to work, > >it keeps displaying a month and days time difference even when tere isnt and >i require to put a select statement within the 2nd variable >"Mike Hodgson" wrote: > > > >>datetime data is not stored internally in SQL Server in US format or UK >>format. It's not a string but some floating point data. How it is >>displayed should be handled at the client, but if you really want to >>force the returned data to some particular string representation you can >>use the CONVERT() >><http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp> >>function with the /style/ parameter. For example, >> >> CONVERT(varchar(10),@mydatetime,103) >> >> or >> >> CONVERT(varchar(30),@mydatetime,131) >> >>Also, since the 2 datetime values are both essentially floating point >>you can simply do floating point arithmetic (subtraction in this case) >>to get your answer. Like this: >> >> declare @d1 datetime >> declare @d2 datetime >> declare @d3 datetime >> >> select @d1 = '20050101' >> select @d2 = getdate() >> >> select @d3 = @d2 - @d1 >> >> select >> datepart(mm,@d3) as Months, >> datepart(dd,@d3) as Days, >> datepart(hh,@d3) as Hours, >> datepart(mi,@d3) as Minutes, >> datepart(ss,@d3) as Seconds >> >>Hope this helps. >> >>-- >>*mike hodgson* >>blog: http://sqlnerd.blogspot.com >> >> >> >>Trigger wrote: >> >> >> >>>I am trying to calculate the time difference between two cells, >>>However I have tried the datediff but it does not display the answer that i >>>require, >>>I am looking for the time diffence to be displayed in months, days, hours, >>>mins and seconds, >>> >>>Can anyone give any help? >>> >>>Also, Is there any way to change the time format that is display when you >>>select the time from the cells, ie, it is sotred in US format but I would >>>like it to be displayed in UK format, >>> >>>thanks for any help >>> >>> >>> >>> > But the days & months are tricky primarily because there are a variable And how about the difference between Jan 31st and Feb 28th, is that one > number of days in each month. For example, is the difference between > '2005-02-28' and '2005-03-30' 0 months & 30 days or is it 1 month & 2 > days? month and 0 days or is it 0 months and 28 days? I have got no idea, i just used what Mike has kindly supplied,
Do you have any idea on how to resolve that? Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > > But the days & months are tricky primarily because there are a variable > > number of days in each month. For example, is the difference between > > '2005-02-28' and '2005-03-30' 0 months & 30 days or is it 1 month & 2 > > days? > > And how about the difference between Jan 31st and Feb 28th, is that one > month and 0 days or is it 0 months and 28 days? > > > >I have got no idea, i just used what Mike has kindly supplied, Well, first, *you* need to decide how to resolve it. My question was not > > Do you have any idea on how to resolve that? one about the code at all, but rather about your rules. You said you wanted to show months, so, stop thinking about the code for a second, and answer the question, is the time that passes between Jan 31st and Feb 28th one month and 0 days, or 0 months and 28 days? What if it's a leap year? It would probably be 0 months and 28days
and for the example between january 31st and march 28th would be 1mnth and 28days I thinks thats what you asked? as you can probably tell , im new to this sql programming Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > >I have got no idea, i just used what Mike has kindly supplied, > > > > Do you have any idea on how to resolve that? > > Well, first, *you* need to decide how to resolve it. My question was not > one about the code at all, but rather about your rules. You said you wanted > to show months, so, stop thinking about the code for a second, and answer > the question, is the time that passes between Jan 31st and Feb 28th one > month and 0 days, or 0 months and 28 days? What if it's a leap year? > > > If I had to choose some rules for displaying elapsed time in Months and
Days, then I would probably use the rule to only count complete calendar months as Months, and all remaining days as Days. If it is assumed that the end date includes the entire day, and the start date excludes the entire day (which is counter intuitive to me), then this rule would give the following results: Jan 31st - Feb 28th (non-leap year): 1 month, 0 days Jan 31st - Feb 28th (leap year): 0 month, 28 days Jan 31st - March 28th: 1 month, 28 days Feb 28th - March 30th (non-leap year): 0 months, 30 days March 1st - April 29th: 0 months, 59 days March 10th - April 10th: 0 months, 31 days March 10th - May 10th: 1 month, 31 days July 10th - September 10th: 1 month, 31 days Of course, this rule would be more complex to calculate... HTH, Gert-Jan Trigger wrote: Show quote > > It would probably be 0 months and 28days > and for the example > between january 31st and march 28th would be 1mnth and 28days > > I thinks thats what you asked? > as you can probably tell , im new to this sql programming > > "Aaron Bertrand [SQL Server MVP]" wrote: > > > >I have got no idea, i just used what Mike has kindly supplied, > > > > > > Do you have any idea on how to resolve that? > > > > Well, first, *you* need to decide how to resolve it. My question was not > > one about the code at all, but rather about your rules. You said you wanted > > to show months, so, stop thinking about the code for a second, and answer > > the question, is the time that passes between Jan 31st and Feb 28th one > > month and 0 days, or 0 months and 28 days? What if it's a leap year? > > > > > > I don't exactly how the end user wants the datetime difference formatted,
but perhaps this will get you started: http://www.aspfaq.com/show.asp?id=2271 Show quote "Trigger" <Trig***@discussions.microsoft.com> wrote in message news:C2AF5DD6-CC2D-400F-9534-D93D127382C7@microsoft.com... >I am trying to calculate the time difference between two cells, > However I have tried the datediff but it does not display the answer that > i > require, > I am looking for the time diffence to be displayed in months, days, hours, > mins and seconds, > > Can anyone give any help? > > Also, Is there any way to change the time format that is display when you > select the time from the cells, ie, it is sotred in US format but I would > like it to be displayed in UK format, > > thanks for any help JT,
Aaron's page on aspfaq deals only with time, which is relatively easy compared to date differences because the intervals are always consistent (always 60 sec/min, 60 min/hr, 24 hrs/day). You can simply, as Aaron shows, find the difference in seconds and extrapolate to get hrs:min:sec. But calculating year/month/day differences are harder because you have to decide how to deal with the fact that the intervals are not consistent (not every month has the same number of days) and then, due to those inconsistent intervals, the calculations become much more complex. -- Show quote*mike hodgson* blog: http:sqlnerd.blogspot.com JT wrote: >I don't exactly how the end user wants the datetime difference formatted, >but perhaps this will get you started: >http://www.aspfaq.com/show.asp?id=2271 > >"Trigger" <Trig***@discussions.microsoft.com> wrote in message >news:C2AF5DD6-CC2D-400F-9534-D93D127382C7@microsoft.com... > > >>I am trying to calculate the time difference between two cells, >>However I have tried the datediff but it does not display the answer that >>i >>require, >>I am looking for the time diffence to be displayed in months, days, hours, >>mins and seconds, >> >>Can anyone give any help? >> >>Also, Is there any way to change the time format that is display when you >>select the time from the cells, ie, it is sotred in US format but I would >>like it to be displayed in UK format, >> >>thanks for any help >> >> > > > > > hrs:min:sec. But calculating year/month/day differences are harder ....in addition to rules determining what to call a month, you also have to > because you have to decide how to deal with the fact that the intervals > are not consistent (not every month has the same number of days) and > then worry about whether your span of time crossed a daylight savings boundary... fun fun...  |
|||||||||||||||||||||||
Other interesting topics