• - Code, Content, and Presentation
• -- Databases
• ---- calculator script - add easter

Jstanfield - 3:37 pm on Dec 9, 2011 (gmt 0)

What if you can return the date of Easter for the current year in your query?

In the past, when I've needed to figure out Easter, I've used the example in the link below, which is easily adapted to different languages.

[usno.navy.mil...]

`select @y:=year(now()) y,@c:= @y DIV 100 c,@n:= @y - 19 * ( @y DIV 19 ) n,@k:= ( @c - 17 ) DIV 25 k,@i:= @c - @c DIV 4 - ( @c - @k ) DIV 3 + 19 * @n + 15 i1,@i:= @i - 30 * ( @i DIV 30 ) i2,@i:= @i - ( @i DIV 28 ) * (1 - ( @i DIV 28 ) * ( 29 DIV ( @i + 1 ) ) * ( ( 21 - @n ) DIV 11 ) ) i3,@j:= @y + @y DIV 4 + @i + 2 - @c + @c DIV 4 j1,@j:= @j - 7 * ( @j DIV 7 ) j2,@l:= @i - @j l,@m:= 3 + ( @l + 40 ) DIV 44 m,@d:= @l + 28 - 31 * ( @m DIV 4 ) d,cast(concat(@y,'-',@m,'-',@d) as date) easter`

Just wrap it in parentheses and add it to your FROM clause, and your CASE statements can refer to easter.easter as if it was a field named easter on a table named easter. Caveat: it uses year(now()) so it assumes you're always expecting to use the current year.

`select some_field,easter.easterfrom some_table, (select @y:=year(now()) y,@c:= @y DIV 100 c,@n:= @y - 19 * ( @y DIV 19 ) n,@k:= ( @c - 17 ) DIV 25 k,@i:= @c - @c DIV 4 - ( @c - @k ) DIV 3 + 19 * @n + 15 i1,@i:= @i - 30 * ( @i DIV 30 ) i2,@i:= @i - ( @i DIV 28 ) * (1 - ( @i DIV 28 ) * ( 29 DIV ( @i + 1 ) ) * ( ( 21 - @n ) DIV 11 ) ) i3,@j:= @y + @y DIV 4 + @i + 2 - @c + @c DIV 4 j1,@j:= @j - 7 * ( @j DIV 7 ) j2,@l:= @i - @j l,@m:= 3 + ( @l + 40 ) DIV 44 m,@d:= @l + 28 - 31 * ( @m DIV 4 ) d,cast(concat(@y,'-',@m,'-',@d) as date)) easterwhere some_table.some_date=easter.easter;+------------+------------+| some_field | easter |+------------+------------+| 1 | 2011-04-24 || 2 | 2011-04-24 || 3 | 2011-04-24 || 4 | 2011-04-24 || 5 | 2011-04-24 || 6 | 2011-04-24 || 7 | 2011-04-24 || 8 | 2011-04-24 |+------------+------------+`