Use Round function for date time field in SQL

Hi Everyone, I have a question about using Round function for Date time fields...what I am trying to do is use this SQL statement to get the difference between dates upto 1 decimal place...the sql code I used is today - t.last_reported_dt AS Dwell_Time_Days ,so I am subtracting Today's date from other dates from a table who's field name is last_reported_dt..this gives me an integer- so instead I tried using this SQL statement ...Round((today-t.last_reported_dt), 1) AS Dwell_Time_Days but this gives me an error saying [Informix]Intervals or datetimes are incompatible for the operation. (0.16 secs) ..If someone can help me out that will be much appreciated! Thanks, Tariq -- Message posted via DBMonster.com http://www.dbmonster.com/Uwe/Forums.aspx/informix/200806/1

Round and Trunc are available for datetime and intervls starting in version 11. Prior to that you can cast and the EXTEND keyword to achieve the same result. John saad_tariq via DBMonster.com wrote: > Hi Everyone, > > I have a question about using Round function for Date time fields...what I am > trying to do is use this SQL statement to get the difference between dates > upto 1 decimal place...the sql code I used is today - t.last_reported_dt AS > Dwell_Time_Days ,so I am subtracting Today's date from other dates from a > table who's field name is last_reported_dt..this gives me an integer- so > instead I tried using this SQL statement ...Round((today-t.last_reported_dt), > 1) AS Dwell_Time_Days but this gives me an error saying [Informix]Intervals > or datetimes are incompatible for the operation. (0.16 secs) ..If someone can > help me out that will be much appreciated! > > Thanks, > > Tariq >

Hi John, it gives me the same error when I use Trunc however when I use Cast and Extend it gives me an error saying syntax error... John Miller wrote: >Round and Trunc are available for datetime and intervls starting >in version 11. Prior to that you can cast and the EXTEND keyword >to achieve the same result. > >John > >> Hi Everyone, >> >[quoted text clipped - 11 lines] >> >> Tariq -- Message posted via http://www.dbmonster.com