How To Get Average Value Of Time Difference?

Apr 2, 2008

My table has two datetime columns (TheatreArivalDate and TheatreDepartDate). Can I get an average value of the time differences of them? Thanks

View 1 Replies


Transact SQL :: Getting The Average Of Time

Apr 30, 2015

I have the following code which returns day of week name, position and date and time of surgeries.


FROM table1The data looks like thisHow can i group this data so I get the average start time by day of week and position?

View 3 Replies View Related

Average Turnaround Time - Datediff() Help

Mar 14, 2007

Hi everyone, I need some help with creating a report that calculates the average turnaround time in days that it takes for units to return from trips destined to a location.

The database that I am working with lists a trip each time a unit is dispatched to a destination, and then another trip is created for the units return. In the example below I am trying to calculate the number of days that it takes for a unit to return to Vancouver by calculating the difference between the departure date from Vancouver and the arrival date back into Vancouver. I then need to calculate the average number of days that it takes for a unit to return from a trip. See sample data below.

================================================== =======
U12 ----001 --- VANCOUVER -------FEB 10 ------ ONTARIO ----- FEB 15
U10 ----002 --- VANCOUVER -------FEB 13 ------ ONTARIO ----- FEB 18
U12 ----003 --- ONTARIO ----------MARCH 13 --- VANCOUVER -- MARCH 18
U10 ----004 --- ONTARIO ----------MARCH 1 ---- VANCOUVER ---MARCH 6

Unit U12 took 36 days to return back to Vancouver
Unit U10 took 21 days to return back to Vancouver

Therefore based on the two trips it takes an average of aproximately 28.5 days for a unit to return from trips destined to Ontario.

View 1 Replies View Related

Average Lock Wait Time

Oct 3, 2014

I keep getting an alert from a Third-Party Tool stating my server is reporting failed components with the following message

Components Reporting Problems:
Average Lock Wait Time(ms)(Critical)

When I look at sp_who2 or sp_lock I don't see anything blocking processes or anything else out of the ordinary.

View 2 Replies View Related

Getting The Average Results Based On Time.

Nov 19, 2006

The table has columns like this.
time smalldatetime
value1 int
value2 int

for example,
'2006-11-16 12:00:00',100,200
'2006-11-16 13:00:00',110,210
'2006-11-16 14:00:00',120,220

The record is inserted at every hour.

I want get daily,monthly,yearly average and display the result ordered by time.

View 1 Replies View Related

Determine Average Time Between Events

Jan 9, 2008

I am trying to figure out how to calculate the average time between phone calls for a user. The initial requirement is to calcualte this on all calls for an entire month but I would guess that would lead to other periods as well, such as daily, weekly, etc. One hurdle is what to do when going from one day to the next. I could possibly just week out any times between calls that are greater than a certain amount of time to address that.
Any way, here is a small sample of what I'll be dealing with. Any ideas on how to approach this or get it to work would be greatly appreciated.

Code Block
CREATE TABLE #avetime (origdate datetime, duration_seconds int, duration_minutes decimal(9,2), phoneuser varchar(20), calltype varchar(15))
INSERT into #avetime VALUES ('Jan 7 2008 9:19AM', 21, 0.4, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 7 2008 9:19AM', 48, 0.8, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 7 2008 9:33AM', 81, 1.4, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 7 2008 9:35AM', 87, 1.5, 'Coleman', 'Internal')
INSERT into #avetime VALUES ('Jan 7 2008 9:37AM', 27, 0.5, 'Coleman', 'Internal')
INSERT into #avetime VALUES ('Jan 7 2008 9:42AM', 13, 0.2, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 7 2008 9:43AM', 84, 1.6, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 7 2008 12:00PM', 914, 15.2, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 7 2008 2:24PM', 3, 0.1, 'Coleman', 'Internal')
INSERT into #avetime VALUES ('Jan 8 2008 10:13AM', 21, 0.4, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 8 2008 10:33AM', 482, 8.0, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 8 2008 11:49AM', 56, 0.9, 'Coleman', 'Long Distance')

View 2 Replies View Related

Average Processing Time With Your Cubes

May 22, 2008

I am running into a barrier and need to understand the average length of time that a fully optimized data cube should take to process.

We are currently running an average of 15 to 20 minutes per cube, with average of 2000 aggregations, 25% performance increase, and approximately 2 million rows, with around 40 dimensions and 30 measures.

I personally think this is a pretty good time to process. However, I am being challenged to reduce this time frame. In theroy I can't possibly see it getting below where we currently are. SO I am reaching out to the group of guru's...

What is your average length of time to process your Data Cubes? Please respond to me at I would greatly appreciate it and need the averages from the field.

View 1 Replies View Related

T-SQL (SS2K8) :: Average Of Time Between Multiple Dates

Oct 7, 2015

I have a dataset as such:

Student TestTypeDate TestCnt
111-22-1111English2015-09-01 10:00:00 1
111-22-1111Math2015-09-02 11:00:00 2
111-22-1111Geo2015-09-03 12:00:00 3
222-11-2222English2015-09-01 10:00:00 1
333-22-1111English2015-09-01 10:00:00 1

[Code] ...

So some have just 1 test and some have multiple. I have a count for each. What I need to do is use that count and get an average time between each test per student.

View 9 Replies View Related

Time Difference

Aug 19, 2005

I want to create a View, which contains columns from two tables, and an additional column, which should calculate the difference between two dates.01/05/2005 7:30 AM - 01/05/2005 8:00 AMThe column should be a calculated column,Can I assign the value of a column to a user defined function?In the UDF, how to get the difference between those two dates to be: "00:30"Are there SQL Server functions to time and date that allows me to do so? I have dateadd, datediff, but I am unable to figure out the 2 problems above.thank you,

View 10 Replies View Related

Avg Time Difference

Aug 21, 2005

In my table i have two cols and datatype datetime, for example they has the values,--------StartDate --------------------------------------- StopDate ------______________________________________________01/05/2005 7:30 AM ------------------------ 03/05/2005 10:00 AM13/05/2005 2:30 PM ------------------------- 01/08/2005 8:00 PM_____________________________________________How do I find out the avg time difference between StopDate and StartDate? I tried AVG(StopDate - StartDate ), but it's giving me the following error message. "The average aggregate operation cannot take a smalldatetime data type as an argument."Thanks for any reply.

View 1 Replies View Related

Need Help In Working Out A Time Difference ..

Oct 23, 2007

I have the following staement :
   AVG( CASE WHEN EventTypeName=''EventA''  THEN CAST(DateDiff(s,EntryTime,EventDateTime) as float) END ) as Duration1
 but the problem here is that i need to find the difference between two EventTypes EventA and EventB  NOT EntryTime and EventDateTime of EventA
 so how can i re-do the above query to get:
EventA - EventB
EntryTime - EventA
 NOTE: EventDateTiem is the time that the EventA occurred.

View 8 Replies View Related

Time Difference Between Two Dates

Apr 10, 2008

advance thanks to all...can anybody helpme soon...........
i am using sql server 2005........i want to find out time difference between two dates in HH,MM,SS format
eg i want the time difference of these two dates
02/Nov/06 9:14:21 AM
19/Apr/07 11:52:31 AM 
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)

View 9 Replies View Related

Date/Time Difference

Apr 23, 2002

I have a table containing following Columns plus other columns:
Start Date 04-01-2002(mm-dd-yyyy) varchar(10)
Start Time column 09:30:30(hh:mm:ss) varchar(8)
End Date 04-05-2002(mm-dd-yyyy) varchar(10)
End Time column 09:45:30(hh:mm:ss) varchar(8)

1]We want to create a view for Report Users So that they could just select
the other columns and difference in start Date/time to End Date /Time.
2]The Difference in time should be based on Business Day. i.e.
Saturday & Sunday should be excluded. &
3]Each business day is defined as 8.00 am to 5 .00pm
( 1 Business day = 9 hrs)
4]The time is stored as 24 hour clock.

For e.g.
Start Date Start TimeEnd Date End Time Desired Output
04/01/2002 10:00:0004/01/2002 3:30:00 0 Days 5:30 Hrs
04/01/2002 16:00:0004/02/2002 10:45:00 0 Days 3:45 Hrs
04/01/2002 09:00:0004/03/2002 10:45:00 2 Days 1:45 Hrs
04/05/2002 16:30:0004/08/2002 9:45:00 0 Days 2:15 Hrs******
******In last case as 04/06/2002 & 04/07/2002 were Saturday & Sunday ,hence
excluded.So the difference of time is just 2:15 hrs

Can you please give me a suggestion/pseudo code/logic on how I might do this?
We cannot use cursors as we want to make a view.This is for SQL Server 2000

View 1 Replies View Related

Finding Time Difference

Jan 15, 2003

I have a start_datetime column and an end_datetime column that records when a process we run starts and stops. I am trying to write a query that can calculate the difference between the start time and the end time and display it as hh:mm:ss but cannot seem to come up with a solution. Any ideas?

View 2 Replies View Related

Time Difference In Sql Server

May 21, 2007

hi to all,
very urgent query

how to minus the two times(hh:mm:ss)in sql server


View 2 Replies View Related

How To Get Sum Of Counter With Time Difference

Dec 9, 2013


DateField--------------CNT-1 CNT-2 CNT-3
2013-12-09 09:00:00.000 8 1 2
2013-12-09 09:01:00.000 3 7 1
2013-12-09 09:02:00.000 1 2 3
2013-12-09 09:03:00.000 2 5 2
2013-12-09 09:04:00.000 1 1 7
2013-12-09 09:05:00.000 2 3 2

My requirement is to get the output in the following format if i take the time with 2 minutes difference

DateField ------------------ Sum-CNT-1 Sum-CNT-2 Sum-CNT-3
09:00:00.000 - 09:02:00.000 12 10 6
09:02:00.000 - 09:04:00.000 3 6 9

Is there anyway i can write down a query to get the sum of the counter with 2 minutes difference. I may take time slot between 9 AM to 10 AM so i should get the sum of the counter with 2 minutes gap in a single query.

View 1 Replies View Related

Time Difference In SQL Server

Dec 6, 2007

I am new to SQL Server and am trying to write a query that subtracts the time difference in the same column. I need to know the time difference between BatchSequence 2, LoadSequence 1 and BatchSequence 13, LoadSequence 3. Below is an example of the data. Thanks in advacne for the help!

13 1Part Number11/1/2007 6:08:02 AM
13 2Scan Trace11/1/2007 6:08:03 AM
13 3Slot Position11/1/2007 6:08:04 AM
2 1Part Number11/1/2007 5:53:06 AM
2 2Scan Trace11/1/2007 5:53:07 AM
2 3Slot Position11/1/2007 5:53:08 AM

View 20 Replies View Related

Calculating Time Difference

Jul 20, 2005

Hi,I have a table called Bookings which has two important columns;Booking_Start_Time and Booking_End_Time. These columns are both of typeDATETIME. Given any day how can I calculate how many hours are availablebetween the hours of 09.00 and 17.30 so a user can see at a glance how manyhours they have unbooked on a particular day (i.e. 8.5 hours less the timeof any bookings on that day), can this be done with a queryor do I have to work it out in my code?Thanks for your help

View 2 Replies View Related

Calculating Time Difference Between Two Different Dates

Dec 9, 2011

I have data in which i need to calculate employees working hours for a day...

name time in_out
manisha 2011-01-01 9:30:00.000am 1
manisha 2011-01-01 10:30:00.000 0
manisha 2011-01-01 10:45:00.000 1
manisha 2011-01-02 1:00:00.000am 0

How can i calculate time in that two dates as 1 is for entry an 0 is for exit..

View 2 Replies View Related

Compare Time Difference Action

Jan 13, 2012

Is it possible to send a sql statement to update column 1 where the current time is 10 mins older than the time in col 2.

Column 1 is holding a true statement that we need to update to false if over 10 mins old...

View 8 Replies View Related

Time Difference Between Sequence Dates?

Feb 27, 2013

It has been a while since I have used SQL server and require to show a time difference between a start and end time, with only 1 date to determine these times

locationdate as LocDate,
locationid as Loc_ID,
locations.loc_name as Location_Name
from current_locations
left outer join locations on
locations.loc_id = current_locations.location_id
where current_locations.Attend_ID = '1234567'

Arrival Date LocDate Loc_IDloc_name
26/02/2013 19:2126/02/2013 19:271270Queue
26/02/2013 19:2126/02/2013 19:341278Dept 1
26/02/2013 19:2126/02/2013 21:10222Dept 2
26/02/2013 19:2127/02/2013 02:5631Left Department

What I want to acheive is the following where the Start location date is either the arrival date or the next location date and the endLocDate is the next finish date.

StartLocDateEndLocDateTime DifferenceDept
26/02/2013 19:2126/02/2013 19:2700/01/1900 00:06Queue
26/02/2013 19:2726/02/2013 19:3400/01/1900 00:07Dept 1
26/02/2013 19:3427/02/2013 02:5600/01/1900 07:22Dept 2

View 4 Replies View Related

Grouping The Records On The Time Difference

May 14, 2008

Hi Guys,

I want to group the records on the time difference

declare @tbl as table(id int,intid int,val int,dt datetime)
insert into @tbl
select 1,1,10,'03/31/2006 15:05:22' union all
select 2,1,12,'03/31/2006 15:10:22' union all
select 3,1,15,'03/31/2006 15:15:22' union all
select 4,1,12,'03/31/2006 15:25:22' union all
select 5,1,8,'03/31/2006 15:30:22' union all
select 6,1,6,'03/31/2006 15:35:22' union all
select 7,1,4,'03/31/2006 15:40:22' union all
select 8,1,3,'03/31/2006 15:45:22' union all
select 9,1,10,'03/31/2006 15:50:22'

declare @tbl1 as table(intid int,Tm int,val int)
insert into @tbl1
select 1,5,10

I want a output such that when the val in @tbl goes below the val in @tbl1 for the Tm mentioned in @tbl1
then the time difference should be shown.For example record 1 it starts with 10 the records remain more than 10 till
record number 5.From 5 the records remains lower than 10 till record number 9.So I need to show the the time
difference from record number 5 till 9.
But there is a catch.In @tbl1 there is column named Tm.The time difference sould be calculated only if the diff
more than Tm value in @tbl1.
For example if the value of Tm is changed to say 25 then the there is no need to show the time difference since the
time difference value from record 5 to record 9 is less than 25.
Hope I am clear.

Please help me out.


View 3 Replies View Related

Minimum Time Difference Between Tables

Sep 3, 2013

I receive the following result set from TableA (In Time)

7/9/2013 9:27:00.000 AM
7/9/2013 10:24:00.000 AM
7/9/2013 11:25:00.000 AM
7/9/2013 1:23:00.000 PM
7/10/2013 7:27:00.000 AM

Then we receive the following result from TableB (Out Time)

7/9/2013 9:30:00.000 AM
7/9/2013 10:29:00.000 AM
7/9/2013 1:37:00.000 PM

We may not always get Out Times in TableB so I want to merge these into one table to have the In Time and Out Time in separate columns in that one table. In this example with the red type those should be In Time and Out Time for mapped unique identifiers from each table and yet the purple color coded example would have an In Time of 11:25 AM and the Out Time would remain as NULL.

I am using this block of code but is not working the way I want it to because the 11:25am In Time is getting mapped to the 1:37pm Out Time.

and out_time = (select min (out_time)
tableB.record# = tableA.record#
and tableB.loc_id = tableA.loc_id

GROUP BY tableB.record#, tableB.loc_id )

It seems I need to focus on the minimum datediff for each record line but can't figure that part out.

View 2 Replies View Related

Calculate The Difference Time Between Two Times?

Oct 9, 2013

i am using this expression to get the time difference between two times.

{%Z.elapsed.time(,@AK.VD.depart.time,,@DV.VD.arrival.time,"hh.hh")*60} as [LOS (min)]

When Arrival time and depart time both are on same day above expression working to get the diference .

But if arrival date 2013-09-20 00:00:00.000 and arrival time 0800 and depart date 2013-09-21 00:00:00.000 and depart time 0050 when i calculate the time difference(using above expression) between these two i am getting -429.60 which is wrong. i have to get around 990.

View 1 Replies View Related

Calculating 'time Difference' Between Two Records....

Dec 21, 2005

I have a data set like so:UTC_TIME Timestamp NodeID Message FlagLineStation11/19/2005 10:45:07 1132397107.91 1 3 5 1028103411/3/2005 21:05:35 1131051935.20 2 3 5 1009104311/25/2005 21:12:16 1132953136.59 3 3 5 10371049I added the UTC_TIME column in as aconversion of the unix timestamp inthe TIMESTAMP column.Keeping things simple and straightforward, I need to be able tocalculate the difference from one record to the next (ordered byTIMESTAMP or UTC_TIME) and output the result into another column in thetable.NODEID is the unique id.First, what is the function to do so if, say, I only wanted tocalculate the difference between 2 records as just a basic SELECTstatement. That way I can answer quick question based on any one or twoNODEID's.Second, how would I further that to continually calculate (as statedabove)?WOuld this be a stored procedure? A trigger? A cursor?I am learning as I go here. Any help is greatly appreciated.R.

View 4 Replies View Related

Linked Server Time Difference

May 31, 2006

I am using SQL 2000 and/or SQL 2005 to link to an Oracle Db usingOPENQUERY.The SQL 2000 box is SP4 on Win2000.I can run a query thru Q.A. for SQL 2000 or I can run an Oracle querythru SQL-Plus to see the data. But, if I run a date-query using thelinked server then SQL Server returns a TIME that is off by 4 hours.SQL Query:SELECT * FROM OPENQUERY( ORASERVER, 'SELECT MYDATE FROM MYTABLE')Oracle Query:'SELECT MYDATE FROM MYTABLE'Oracle results:04/16/2006 01:07:00SQL 2000 result:04/16/2006 05:07:00Has anyone seen this before? Is there a fix?I have searched the newsgroups and MS Kb but can not find an answer.Thanks,Marty

View 1 Replies View Related

Calculating Time Difference In Msdb..sysjobhistory

Dec 28, 2006

I am setting up a monitor to alert me if an SQL job has failed in the "last 20 minutes". This should run 24 hours a day, 7 days a week. My query looks something like this.

select * from TALMAIN.msdb.dbo.sysjobhistory where job_id = '7139D5D1-CD88-46E8-8324-5D5A0D8D3A27' and run_status <> 1 and
DATEPART(YYYY,GETDATE()) = substring(convert(char(8),run_date),1,4)and
DATEPART(MM,GETDATE()) = substring(convert(char(8),run_date),5,2) and
DATEPART(DD,GETDATE()) = substring(convert(char(8),run_date),7,2)and DATEPART(HH,GETDATE()) = substring(convert(char(8),run_time),1,2)and (DATEPART(MI,GETDATE()) - substring(convert(char(8),run_time),3,2)) <= 20.

The run_date and run_time columns in msdb..sysjobhistory are stored as integers. Tried a couple of things, but I am unable to convert both of them to datetime data type. The last conditions in the above logic hold true for only "2 digit" hour and minute values.

DATEPART(HH,GETDATE()) = substring(convert(char(8),run_time),1,2)and (DATEPART(MI,GETDATE()) - substring(convert(char(8),run_time),3,2)).

What about time values like 00:05 AM and single digit time values like 1:00 AM and 9:05 AM, for example?. I pasted some sample run_date and run_time values from sysjobhistory below.

run_date run_time

2006122821510 -- 02:15:10 AM (how to get the minute count?)
2006122821510 -- 02:15:10 AM (same as above)
20061227233014 -- 23:30:14 PM (this is strt forward)
20061227233014 -- 23:30:14 PM (same as above)
200612273016 -- 00:30:16 AM (how to get minute count?)
200612273015 -- 00:30:15 AM (how to get minute count?)

Is there a simpler logic to achieve this? Hope I was clear, else let me know. Please advise. Thank you.

View 5 Replies View Related

Transact SQL :: Getting Time Difference In Hours And Minutes?

Jul 10, 2015

Currently my script is using the below mentioned query to find the time difference.

DATEDIFF(HH,DATEADD(SS,hcreacion,fcreacion) ,DATEADD(SS,hcerrar,fcreacion))

If there is 1 hr 30 minutes time difference, I am getting 2 hours as output. But we need 1.30 as output. is there any way to achieve this?

View 14 Replies View Related

Transact SQL :: Total Difference Time In Last Column?

May 23, 2015

I have the below query

DECLARE @GivenDate DATE='2015-05-13'
create table #table (LedgerID int,AttDate Date, checkedtime time,checkedtype varchar(1))
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','09:01:48.0000000','I')
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','13:05:52.0000000','O')
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','14:10:25.0000000','I')


the result is like below

i need 'TotalDiffernceTime' column as new column (OUT1-IN1)+(OUT2-IN2).

i am using SQLServer 2008 R2

View 8 Replies View Related

Transact SQL :: Getting Time Difference Between Same Column In Different Rows

Jun 21, 2015

I have a table data like below

id         type      timestamp
1001    start1    10:34:23:545
1001    start2    10:34:24:545
1001    end2     10:34:24:845
1001    end1     10:34:25:545
1002    start1    10:34:25:645
1002    start2    10:34:25:745
1002    end2     10:34:25:945
1002    end1     10:34:25:965

I need the result as follows

id              millisecond diff start1end1                 millisecond diff start2end2
1001    end1 timestamp-start1 timestamp    end2 timestamp-start2 timestamp
1002    end1 timestamp-start1 timestamp    end2 timestamp-start2 timestamp

SQL Server 2008 R2

View 5 Replies View Related

How To Get Time Time Difference In Sql?

Nov 30, 2007

Could any one please tell me how to find time difference between two dates?
i have two fields in database as datetime data type.i need to get time difference between this two to do that?
i use this one
SELECT outdate, (datediff(mi, outtime, intime)  / 60.0)AS TimeUtilized FROM breaktime
but it giving me results as
i jus want it to be
How to do this?
Thanks for any help.

View 5 Replies View Related

Time Difference Sotored Proc &> Job &> Seperate Query

Dec 4, 2002

I have seen in this forum that many people has posted like my query time taken problem. But I couldn't get clear solution for this.

Here is the my issue:-
I have written couple of batch query to bring data from another sql server.
I have used proper index and index hints for these query.
I have used SET NOCOUNT ON in stored procedure.

I am having the time difference for one of the blow qeury in various running process that executed in QA as single query , stored procedure for whole batch query and schedule this stored procedure in job

sotored Proc = 31 second > job =28 second > seperate query = 12 second.

one of 15 batch query

declare @last smalldatetime
set @last ='11/08/2002'

SET RESP_BUYER_FST_NAME = i.first_name,
RESP_BUYER_LST_NAME = i.last_name
FROM oesd.dbo.individual i WITH (NOLOCK index(XPKindividual))
INNER JOIN Leap.dbo.fnIndBuyer(@last) fn
ON fn.individual_num=i.individual_num
WHERE ENTERPRISE_NUM = fn.enterprise_num

Anbody can able to give proper reason why I am getting this time difference.


View 3 Replies View Related

Returning Time Difference As Days/ Hours/ Minutes

Sep 16, 2005

I have written a function that returns the number of Days, Hours and minutes from a given number of minutes. On testinf the results are close but not quite there. Can anyone see where I have gone wrong or is there an easier way of doing this? Code is as follows:

CREATE FUNCTION dbo.GetTimeBetweenLong
(@StartTime DateTime, @EndTime DateTime, @CurrentDate DateTime)
RETURNS VarChar(50) AS
DECLARE @TotalTime Numeric
DECLARE @Minutes Numeric
DECLARE @Hours Numeric
DECLARE @Days Numeric
DECLARE @MinutesInDays Numeric

SET @Days = DATEDIFF(Day, @StartTime, @CurrentDate)
SET @Hours = DATEDIFF(Hour, @StartTime, @CurrentDate) - (@Days * 24)
SET @Minutes = DATEDIFF(Minute, @StartTime, @CurrentDate) - ((@Days * 24)*60) - (@Hours * 60)
SET @Days = DATEDIFF(Day, @StartTime, @EndTime)
SET @Hours = DATEDIFF(Hour, @StartTime, @EndTime) - (@Days * 24)
SET @Minutes = DATEDIFF(Minute, @StartTime, @EndTime) - ((@Days * 24)*60) - (@Hours * 60)

IF(@Days <0)
SET @Days = @Days - @Days - @Days

IF (@Hours < 0)
SET @Hours = @Hours - @Hours - @Hours

IF (@Minutes <0)
SET @Minutes = @Minutes - @Minutes - @Minutes

RETURN CONVERT(nVarChar(10),@Days) + ' Days, ' + CONVERT(nVarChar(5), @Hours) + ' Hours, ' + CONVERT(nVarCHar(5), @Minutes) + ' Mins'


View 1 Replies View Related

Copyrights 2005-15, All rights reserved