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.
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.
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).
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.