Archives of the TeradataForum

Message Posted: Mon, 22 Feb 2010 @ 21:43:34 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I have a V2R6 instance and yes, the query below returns 1's and 0's (which start at "tomorrow's date" last year).

I believe the change you are observing is deliberate and is intended to align Teradata with ANSI.

There may be other options, but you could join your dates to sys_calendar and leverage the day_of_calendar values to work out the number of days between the dates, then divide by 365.25 (Note that this will be an approximate age).

Another approach (which I didn't try because I don't have TD12 right now) is to subtract the dates as per your query then subtract another 1 from the result if the current month is > the birth_date month or the current month = the birth date and the current day is > the birth_date day.

This sounds quite convoluted - if it were me, I'd build it as a UDF then all you need do is call the UDF (which would be a much easier change to propagate through your queries).

If you are comfortable with UDF's, this will be an easy one to build. If you are new to UDF's, you might want to get some assistance. If you are competent with C/C++ but new to UDF's, you could have a look at my article on UDF's at

http://developer.teradata.com

Regards

Teradata Corporation
Glenn McCall
Canberra Australia

	<Prev	Next>		<<First	<Prev	Next>	Last>>