１年分の年月日を生成する

１年分の年月日を生成する場合、大抵はExcelを使ったり、プログラムを作って生成しまう場合が多い。

この１年分の年月日をSQLで生成生成する場合は、以下のようなSQL文を発行すれば生成できる。

 /*１年分の年月日生成*/
 select date_format(date_add('20100101', interval(number) day), '%Y%m%d') as yyyymmdd
 from (
   select hundred.n + tens.n + ones.n as number
   from (
     select 0 as n union all
     select 1 as n union all
     select 2 as n union all
     select 3 as n union all
     select 4 as n union all
     select 5 as n union all
     select 6 as n union all
     select 7 as n union all
     select 8 as n union all
     select 9 as n
   ) as ones cross join (
     select 0 as n union all
     select 10 as n union all
     select 20 as n union all
     select 30 as n union all
     select 40 as n union all
     select 50 as n union all
     select 60 as n union all
     select 70 as n union all
     select 80 as n union all
     select 90 as n
   ) as tens cross join (
     select 0 as n union all
     select 100 as n union all
     select 200 as n union all
     select 300 as n
   ) as hundred
 ) as dummy
 where date_format(date_add('20100101', interval(number) day), '%Y%m%d') < '20110101'
 order by yyyymmdd asc;

データベースに接続できる環境ならば、上記のSQL文をプログラムを書くよりも早いかもしれない。