Problem regarding the difference between two dates in terms of month

General
Enthusiast

Problem regarding the difference between two dates in terms of month

Hi Freinds,

I m executing the following query,

select (date '2010-07-31' - date '2010-06-30')month

and getting the result as

(2010-07-31 - 2010-06-30) MONTH
-----------------------------------------------
1

and

aslo after executing the following query

select (date '2010-07-30' - date '2010-06-30')month

(2010-07-30 - 2010-06-30) MONTH
-----------------------------------------------
1

observe the the two queries

in the first query the difference between the dates '2010-07-31' and '2010-06-30' and the result is "1"

and

in the first query the difference between the dates '2010-07-30' and '2010-06-30' and the result "1"
which is wrong. because the diference between the dates in 2nd query should come "0" but it is coming "1"

how it is possible????????

can anyone help me to figure out this proble?????

5 REPLIES
Enthusiast

Re: Problem regarding the difference between two dates in terms of month

also .... i want the difference between two dates as fraction value....

like
for less than one month.....0.65 or somthing
for grater than one month ...1.45 or something...

how do I get this result..????

Re: Problem regarding the difference between two dates in terms of month

It is calculating on the basis on combination of year and month.
Enthusiast

Re: Problem regarding the difference between two dates in terms of month

Hi khollam:

Try divide the days by 30.44 (the mean days of a year).

select cast (date '2010-07-30' - date '2010-06-30' as integer) / 30.44

Regards.

Re: Problem regarding the difference between two dates in terms of month

Since you are casting the date difference to month, Teradata handles you query in the following way

SELECT EXTRACT (MONTH FROM DATE '2010-07-30' ) - EXTRACT (MONTH FROM DATE '2010-06-30' )

For the fraction part you can calculate the number of days and divide it by either 30.44 or you can include the case logic for each month.

--Calculates the number of days between two given dates
SELECT CAST((DATE'2010-07-30'- DATE'2010-06-30') AS INTERVAL DAY)

Regards,
Kamran

Junior Contributor

Re: Problem regarding the difference between two dates in terms of month

Blame Standard SQL for that calculation :-)

One of the available Oracle UDFs is MONTHS_BETWEEN, which is what you probably need.

Dieter