hello all,

does anyone know of a way to find difference between two dates in
terms of days, months and so on. I will like to find out no. of months
between two given dates. Is that possible.

TIA

deu

Hi Deu,

Teradata has a view called Sys_Calendar.Calendar.

This has many good attributes and can be easily used to solve your problem.

type 'show view sys_calendar.calendar;' to see the attributes or 'help view sys_calendar.calendar' to get an explanation of the attributes.

Check it out, if you need more info give me an example and I will try and write a sql script to solve it for you.

Regards

Heloo OraCool,

Thanks for ur reply. Suppose i have two dates say 10th Dec 2001 and 14th Jan 2002 i need to find the no of months between these two dates which is 1 whereas if the given dates are 10th Dec 2001 and 4th Jan 2002 it should be 0 coz it hasn't come to 10th Jan to be exactly one month. I need this routine to update the no. of payment dues for customers on a daily basis in a given table. Any help will be highly appreciated.

Warm Regards

Deu

Hi Deu

Teradata has the facility of doing simple arithmetic sums or subtractions on date functions and it returns number of days as output.

Assuming we have a table datetest and we want a list of customers where  difference between dtofpay and dtofjoin is greater than 30 days or 1 month.

sel * from datetest;

 *** Query completed. 3 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.

    cust_id  dtofjoin   dtofpay
-----------  --------  --------
         30  01/11/24  02/01/12
         20  01/12/24  02/01/12
         10  01/12/10  01/12/11

The following SQL will work

sel * from datetest where (dtofpay-dtofjoin) > 30;

 *** Query completed. One row found. 3 columns returned.
 *** Total elapsed time was 1 second.

    cust_id  dtofjoin   dtofpay
-----------  --------  --------
         30  01/11/24  02/01/12

Hi Deu

I guess Rohit's suggestion works for you, if you need more on that please let me know.

Regards

Hi,
   It depends on what version of Teradata you are running.  Given the above example will return the number of DAYS between the 2 dates.

Teradata now supports the ANSI Interval specifier.

sel ( date1 - date2 ) month(4);

This tell Teradata you want the answer as an INTERVAL MONTH.

Unfortunately the (4) is the number of DIGITS you want in your answer.   This limits the maximum number of months in the specification to 9999 months ( or about 27 years ).  

This is a limit imposed by Teradata because we use the same parse routine for Years and Months and ANSI limits years to (4) or 9999 years.

I have a request in to up this limit for MONTHS.

please see   

    Thread328-150396

for a further explanation of the interval specification.

If you exceed the number of digits you will get an error
saying date overflow.  for example

   sel date - ( date - 365)) month(1);

would overflow because 12 can't fit in 1 digit (0-9).