Subject Re: [firebird-support] EXTRACT(WEEK FROM DATE) - how to get the right year?
Author Dom Scarlatti
At 06:34 AM 20/12/2008, you wrote:
>Hi all,
>I have seen the discussion in April about the correctness of the
>EXTRACT(WEEK ...) function in FB 2.1.
>On top of that I have a question: In some years, the last days of the
>year part of the week of the next year. The EXTRACT() function works
>correct in my understanding, but how can I associate the right year to it?
>*) 28.12.2008 -> Week 52 of year 2008
>*) 29.12.2008 -> Week 01 of year 2009
>I am looking for a WeekOfTheYear() function. Maybe somebody has already
>mastered this question.

You can do it with an expression...

>The possibility to remove actualy redundant data (week of the year) and
>replace it by a computed field is very tempting.

Yes, it is, as long as you won't want to search on it. ;-)

alter table xyz
add WeekOfTheYear
computed by (

when (extract(month from CertainDate) = 12)
and (extract(week from CertainDate) = 1)
'Week '||extract (WEEK from CertainDate)||' of year '
|| (1 + (extract( year from CertainDate)))

else 'Week '||extract (WEEK from CertainDate)||' of year '
||extract( year from CertainDate)
end )