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>>

Attachments

Library

Quick Reference