I want to write one query to get serial dates, like 1/1/2004, 1/2/2004... I know repeating "select dateadd(d,n,'1/1/2004')" can get the result. But how about a simple method?

My favourite solution for this type of problem uses an integers table. Here's the code to create it:

create table integers (i integer)
insert into integers (i) values (0)
insert into integers (i) values (1)
insert into integers (i) values (2)
insert into integers (i) values (3)
insert into integers (i) values (4)
insert into integers (i) values (5)
insert into integers (i) values (6)
insert into integers (i) values (7)
insert into integers (i) values (8)
insert into integers (i) values (9)

Those 10 rows are sufficient to create, with one or more cross joins, whatever range of integers you need. For example, you could generate the numbers 0 through 999 with a triple cross join like this:

select 100*hundreds.i + 10*tens.i + units.i as iii 
  from integers hundreds
     , integers tens
     , integers units

Why iii? Because that reminds me that it's a number up to 999. Note that the query uses table

    Requires Free Membership to View

    Login

list syntax without join conditions, thus joining every row from all three "copies" of the integers table with every other row. This table list syntax for a cross join will work in any database. In effect, it is the same query as the following one using explicit JOIN syntax:

select 100*hundreds.i + 10*tens.i + units.i as iii 
  from integers hundreds
cross
  join integers tens
cross
  join integers units

The words hundreds, tens, and units are table aliases, required to distinguish the rows from the three different "copies" of the table that are present in the query. Usually I write h, t, and u, to make typing easier. Even more convenient is a view for this query:

create view integers999 (iii)
as
select 100*h.i+10*t.i+u.i 
  from integers h
cross
  join integers t
cross
  join integers u

Now you can use the iii column in an expression to produce all the dates for this year, using a WHERE clause to restrict the upper end of the range:

select dateadd(d,iii,'2004-01-01') as thedate
  from integers999
 where iii <= 365

Simple, eh? Note that there are 366 days in 2004, so '2004-01-01' + 0 days will be the first date, and '2004-01-01' + 365 days will be the last.


This was first published in January 2004

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top