Date function

IS there any way in Sybase which will give me the difference of two datetime fileds ? DateDiff truncates and is not rounding off.  eg: 136 minutes should be converted to 2:16  
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


There is only one way in ASE up to 11.9.x

1) DateDiff returns integer.

2) You can use DateAdd function to add the result of DateDiff to a certain date - then display only the hours, minutes and seconds (or wahtever you want)

dateadd(datepart, datediff expression, startdate)

a) you have to use convert function to get result from isql in readable format ('108' is hh:mm:ss - refer to display formats for date/time)
b) the dateadd and datediff dateparts must be same
c) you can coose whatever 'start date' you want, but remember, that datetime datetime starts on 1.1.1753.

select convert(char(50),dateadd(ss,datediff(ss,'12/31/1999 01:01:01','1/1/2000'), '1/1/1900'),108)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
An in ASE 12.0 you can write your own Java function to convert the 136 to 2:16.
venkatsrAuthor Commented:
Thanks dude. It works for difference less then 24 hours but not more than a day. I have a situation where my calculation time extends for 3-6 days.  
Well, in case of 3+ day difference will command 'dateadd(datepart, datediff expression, '1/1/1900')' result in DATE '4/1/1900 15:06:30.3445'. I have used the convert command to get the difference into a readable format (the result was DATE '1/1/1900 22:58:59'.
You can use date display format 109 to see the full date. And in application you have to substract the 1900 years, 1 month and 1 day to get the result.
Or you can add to the 24-hrs datediff one more datediff counting days - select datediff(dy, date1, date2), convert(char(50),dateadd(ss,datediff(ss, date1, date2), '1/1/1900'),108).
venkatsrAuthor Commented:
Thanks buddy .It works like a charm.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.