Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 06 Mar 2009 @ 16:54:23 GMT


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


Subj:   Re: Which part of the year
 
From:   Dieter Noeth

Anomy.Anom wrote:

  This time I'd like to know which part of the year is already over. The following query:  


          > select (date-cast(extract(year from date) || '-01-01' as date))
          >    / 365.00000
  works fine, but it's not so easy to understand and it's not correct for leap years (divison by 366)  


You need that as a fraction of the year?


  Browsing the Teradata-Forum I found this code:  


          >sel  date (format 'ddd')(char(3));
  which would be perfect for my task, unfortunately it's not possible to calculate with it (divison by 365 is not possible).  


You just have to add brackets:

     SEL  (DATE (FORMAT 'ddd')(CHAR(3))) / 365;

But it's still not correct for leap years :-)

Try

     SELECT calendar_DATE AS d,
        CAST(d - CAST(d / 10000 * 10000 +   101 AS DATE) + 1 AS DEC(8,5))/
                (CAST(d / 10000 * 10000 + 10101 AS DATE) -
                 CAST(d / 10000 * 10000 +   101 AS DATE))
     FROM sys_calendar.calendar
     WHERE EXTRACT(YEAR FROM calendar_date) IN (2008, 2009)
     ORDER BY 1

Depending on your needs you have to remove the "+ 1".

If you have to calculate that often, better pre-calculate it once in your business calendar.


Dieter



     
  <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: 27 Dec 2016