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?

[Webinar] Streamline your web hosting managementRegister Today

jkotekConnect With a Mentor Commented:

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)
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.
All Courses

From novice to tech pro — start learning today.