Solved

computing date/time difference in MS SQL

Posted on 2006-11-10
11
2,756 Views
Last Modified: 2008-03-06
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!
0
Comment
Question by:ayeen
11 Comments
 
LVL 17

Expert Comment

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

0
 
LVL 17

Expert Comment

by:HuyBD
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

by:Aneesh Retnakaran
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 28

Accepted Solution

by:
imran_fast earned 100 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

by:ayeen
ID: 17924758
hi imran_fast

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

0
 
LVL 1

Author Comment

by:ayeen
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


thanks again in advance!
0
 
LVL 1

Author Comment

by:ayeen
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now