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  
venkatsrAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
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)

Synax:
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.

Example:
select convert(char(50),dateadd(ss,datediff(ss,'12/31/1999 01:01:01','1/1/2000'), '1/1/1900'),108)
0
 
jkotekCommented:
An in ASE 12.0 you can write your own Java function to convert the 136 to 2:16.
;-)
0
 
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.  
0
 
jkotekCommented:
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).
0
 
venkatsrAuthor Commented:
Thanks buddy .It works like a charm.
0
All Courses

From novice to tech pro — start learning today.