Archives of the TeradataForum
Message Posted: Fri, 06 Mar 2009 @ 16:54:23 GMT
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
|