Solved

# computing date/time difference in MS SQL

Posted on 2006-11-10
Medium Priority
2,765 Views
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

0
Question by:ayeen
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 17

Expert Comment

ID: 17920179
select datediff(millisecond,start_date,finish_date) as duration from wf_historystep

0

LVL 17

Expert Comment

ID: 17920188
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...
0

LVL 75

Expert Comment

ID: 17920193
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 )

0

LVL 28

Accepted Solution

imran_fast earned 400 total points
ID: 17920207
need to do something like this

select

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'
ELSE
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'
END
AS MONTH_DAYS

from yourtable

0

LVL 1

Author Comment

ID: 17924758
hi imran_fast

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

0

LVL 1

Author Comment

ID: 17924809
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

0

LVL 1

Author Comment

ID: 18261269
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),datepart(day, finish_date)-datepart(day, start_date)) + convert(varchar(5),datepart(hour, finish_date) - datepart(hour,start_date)) as Duration

from mytable
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides? Â Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff lâ€¦
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be \$37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
###### Suggested Courses
Course of the Month10 days, 15 hours left to enroll