SQL: Get a list of dates

By Eric Downing. Filed in Programming, SQL  |  
TOP del.icio.us digg

So I didn’t have a table of dates set up, but I needed to get a list of dates going
back a certain number of days. I was looking at programming up a date function but
decided that a little SQL could do just what I wanted. I used the following query
to generate my list:

select (current_date-21) +s.a as date from generate_series(0,21) as s(A);

This returned a list of dates:

2012-06-01
2012-06-02
2012-06-03
2012-06-04
2012-06-05
2012-06-06
2012-06-07
2012-06-08
2012-06-09
2012-06-10
2012-06-11
2012-06-12
2012-06-13
2012-06-14
2012-06-15
2012-06-16
2012-06-17
2012-06-18
2012-06-19
2012-06-20
2012-06-21
2012-06-22

This dealt with end of month issues and end of year issues without having to generate a ton
of code to do the same thing.

Tags: ,

Leave a Reply