Home Page for the TeradataForum

Archives of the TeradataForum

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

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

Subj:   Re: Char to date in teradata
From:   Curley, David

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


  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 23 Jun 2019