How to calculate days from the difference of two fields.

[rubamushtaq]

Hi, I have been reading all the answers on calculating date difference but I could not get it yet. Can any one tell me how can I get the days from the difference of two timestamps fields in ireport.

I would like to show my sql query,

 

 

   1.      Nvl((Select Max(c.finish_date)

              from code_det cd, os_historystep c

              where cd.module_code = 'RPT'

               ),'') appReciveddt,-- This is first date field.

 

2.          Nvl((Select Max(c.start_date)

              from code_det cd, os_historystep c

              where cd.module_code = 'RPT'

               ) ),'') creditristdt,--This is second date field. From table.....

 

I need to get the days=creditristdt - appRecivedd. I want the "days" as a field in sql .I do not want to store the days in variable. Kindly tell me how to acheive this?

 

 

Ruba

 

 

 

[mgeise]

From Here:  http://www.w3schools.com/sql/func_datediff_mysql.asp

 

MySQL DATEDIFF() Function

---

http://www.w3schools.com/images/up.gif MySQL Date Functions

---

Definition and Usage

The DATEDIFF() function returns the time between two dates.

Syntax

DATEDIFF(date1,date2)

Where date1 and date2 are valid date or date/time expressions.

Note: Only the date parts of the values are used in the calculation.

---

Example

The following SELECT statement:

SELECT DATEDIFF('2008-11-30','2008-11-29') AS DiffDate

will result in this:

DiffDate
1

Example

The following SELECT statement:

SELECT DATEDIFF('2008-11-29','2008-11-30') AS DiffDate

will result in this:

DiffDate
-1

 

[rubamushtaq]

The very functions of DateIff always shows an error that DATEDIFF is invalid identifier. Kindly note that I am using oracle sql developer.I wonder would that funtion be supported?.I have checked the type of feilds ,they are valid dates and they get printed but whenever I take difference ,I get an error.Anyhow I really do appreciate your response.