Archives of the TeradataForum

Message Posted: Fri, 29 Feb 2008 @ 21:34:01 GMT

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

Right there with you on that Oracle code, Michael. The gruesome truth is that it

1) turns the date into a 4-character string of just the year,

2) turns that string into a number

3) subtracts one

4) turns in into a string

5) puts "31-dec-" on the front (well, he left out the second dash, but you get the point)

6) turns it into a date, the last day of the prior year.

That's less than optimal on any DB.

Add_months() wouldn't work, plus it's dangerous to move between Oracle and Teradata using add_months, since they behave differently. In Oracle, if the date you pass is the last day of the month, it will always return the last day of the target month. So add_months('29-feb-2008',1) = 31-mar-2008 in Oracle, not 29-mar-2008.

I guess I wasn't clear enough about the math approach - it returns the integer value of the last day of the prior year, not a number that should be formatted to look like a date.

     select (current_date/10000 -1) * 10000 + 1231 = 1071231
     select (current_date/10000 -1) * 10000 + 1231 (date) = 12/31/2007

Should yours be formatted a string first?

     select (current_date/10000+1899) * 10000 + 1231 (date) = 12/31/3907
     select cast(cast((current_date/10000+1899) * 10000 + 1231 as char(8)) as
     date format 'yyyymmdd') = 12/31/2007

Dave

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