Archives of the TeradataForum
Message Posted: Fri, 29 Feb 2008 @ 21:34:01 GMT
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
|Copyright 2016 - All Rights Reserved|
|Last Modified: 23 Jun 2019|