Wednesday, August 09, 2006

Get All Days Of A Year Using A Single SELECT

Today, Stefan Berner showed me a very handy use of the record generator I have described in my other post Generate A Fixed Number Of Records: retrieve all days of a year using a single select statement.

Restrict the number of records generated to the number of days in a particular year by getting the "day of year" of December 31st: to_char(to_date('3112'||:year,'ddmmyyyy'),'ddd').

Convert the record number (level) back to a date: to_date(level||'.'||:year,'ddd.yyyy').

I use a bind variable in the example below instead of a substitution variable for no other reason than to have an example online :)

-- define a bind variable for the year
variable year number
-- assign a year to the bind variable
execute :year := 2006

-- select all days of the year
select to_date(level||'.'||:year,'ddd.yyyy') day
from dual
connect by level
<= to_number

No comments: