computing date/time difference in MS SQL


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:

1900-01-01 00:29:52.813
1900-01-02 01:00:05.000

when it should be:

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:

0  00:29:52.813
1  01:00:05.000

thanks in advance!
Who is Participating?

Improve company productivity with a Business Account.Sign Up

imran_fastConnect With a Mentor Commented:
need to do something like this


DATEDIFF(SS, start_date,end_date ) AS SECOND_LEFT,
                        DATEDIFF(dd, start_date,end_date )-1 AS DAYS_LEFT,
                        CASE WHEN CAST(DATEDIFF(DAY, DATEADD(MONTH,DATEDIFF(MONTH, start_date,end_date ),start_date),end_date )  AS VARCHAR(10)) > = 0 THEN  
                        CAST(DATEDIFF(MONTH, start_date,end_date ) AS VARCHAR(10)) +' MONTH '+  
                        CAST(DATEDIFF(DAY, DATEADD(MONTH,DATEDIFF(MONTH, start_date,end_date ),start_date),end_date )  AS VARCHAR(10))  +' DAYS'
                        CAST(DATEDIFF(MONTH, start_date,end_date )-1 AS VARCHAR(10)) +' MONTH '+  
                        CAST(DATEDIFF(DAY, DATEADD(MONTH,DATEDIFF(MONTH, start_date,end_date )-1,start_date),end_date )  AS VARCHAR(10))  +' DAYS'
                        AS MONTH_DAYS

from yourtable

select datediff(millisecond,start_date,finish_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...
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Aneesh RetnakaranDatabase AdministratorCommented:
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 )

ayeenAuthor Commented:
hi imran_fast

i like ur solution but how about for the time difference?

ayeenAuthor Commented:
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!
ayeenAuthor Commented:
hi mod,

i was able to solve this myself but i would like to give 50 points to imran_fast

here's my solution:

   start_date as StartDate,
  finish_date as EndDate,
convert(varchar(5),datepart(day, finish_date)-datepart(day, start_date)) + convert(varchar(5),datepart(hour, finish_date) - datepart(hour,start_date)) as Duration

from mytable
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.