ayeen
asked on
computing date/time difference in MS SQL
hi.
i need to create an sql statement or script that would compute the difference between dates.
here are example data in my workflow table (the data type for my dates are datetime:
step end_date start_date
------ ------------- ------------
step1 2006-10-29 15:13:27.750 2006-10-29 14:43:34.937
step2 2006-10-06 09:25:25.000 2006-10-05 08:25:20.000
i tried doing this:
select finish_date-start_date as duration from wf_historystep
but it gave me this result:
duration
-----------
1900-01-01 00:29:52.813
1900-01-02 01:00:05.000
when it should be:
duration
-----------
0 00:29:52.813 (0 day, 29 min, 52 sec and 813 millisec)
1 01:00:05.000 (1 day, 1 hr, 0 min, 5 sec and 0 millisec)
how am i going to reconstruct my sql so that my result would be:
duration
-----------
0 00:29:52.813
1 01:00:05.000
thanks in advance!
i need to create an sql statement or script that would compute the difference between dates.
here are example data in my workflow table (the data type for my dates are datetime:
step end_date start_date
------ ------------- ------------
step1 2006-10-29 15:13:27.750 2006-10-29 14:43:34.937
step2 2006-10-06 09:25:25.000 2006-10-05 08:25:20.000
i tried doing this:
select finish_date-start_date as duration from wf_historystep
but it gave me this result:
duration
-----------
1900-01-01 00:29:52.813
1900-01-02 01:00:05.000
when it should be:
duration
-----------
0 00:29:52.813 (0 day, 29 min, 52 sec and 813 millisec)
1 01:00:05.000 (1 day, 1 hr, 0 min, 5 sec and 0 millisec)
how am i going to reconstruct my sql so that my result would be:
duration
-----------
0 00:29:52.813
1 01:00:05.000
thanks in advance!
select datediff(millisecond,start _date,fini sh_date) as duration from wf_historystep
you can use other datepart for millisecond, e.g: day, minute, second....
get diff in millisecond, you can use divide operator to get day, munite, second...
get diff in millisecond, you can use divide operator to get day, munite, second...
For milliseconds, the maximum number is 24 days, 20 hours, 31 minutes and 23.647 seconds. For seconds, the maximum number is 68 years. So if you are sure that if you take the the datediff wont go beyond the abve mentioned values go for MilliSecond or Minute
refer books online for more options
DATEDIFF ( ms , startdate , enddate )
refer books online for more options
DATEDIFF ( ms , startdate , enddate )
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hi imran_fast
i like ur solution but how about for the time difference?
i like ur solution but how about for the time difference?
ASKER
i tried to use these functions:
datepart(day, finish_date)-datepart(day, start_date) as dayctr,
datepart(hour, finish_date) - datepart(hour,start_date) as hrctr
but i get negative values for the case when the hours for the start_date is greater than the finish_date (in case of military time), like the data below:
start_date finish_date dayctr hrctr
------------- ------------- --------- -------
2006-10-28 20:42:32.037 2006-10-29 11:21:52.150 1 -9
how can i make the hrctr become 15 instead of -9? how can i do this in sql:
hrctr = hour_end - hour_start
if hrctr < 0
then duration = hrctr - 24
ex. data:
hour_end = 11
hour_start = 20
hrctr = 11 - 20
hrctr = -9
duration = -9 - 24
duration = 15
thanks again in advance!
datepart(day, finish_date)-datepart(day,
datepart(hour, finish_date) - datepart(hour,start_date) as hrctr
but i get negative values for the case when the hours for the start_date is greater than the finish_date (in case of military time), like the data below:
start_date finish_date dayctr hrctr
------------- ------------- --------- -------
2006-10-28 20:42:32.037 2006-10-29 11:21:52.150 1 -9
how can i make the hrctr become 15 instead of -9? how can i do this in sql:
hrctr = hour_end - hour_start
if hrctr < 0
then duration = hrctr - 24
ex. data:
hour_end = 11
hour_start = 20
hrctr = 11 - 20
hrctr = -9
duration = -9 - 24
duration = 15
thanks again in advance!
ASKER
hi mod,
i was able to solve this myself but i would like to give 50 points to imran_fast
here's my solution:
select
start_date as StartDate,
finish_date as EndDate,
convert(varchar(5),datepar t(day, finish_date)-datepart(day, start_date)) + convert(varchar(5),datepar t(hour, finish_date) - datepart(hour,start_date)) as Duration
from mytable
i was able to solve this myself but i would like to give 50 points to imran_fast
here's my solution:
select
start_date as StartDate,
finish_date as EndDate,
convert(varchar(5),datepar
from mytable