Interesting date query I need some help with

The way our "calendar" table works is that the primary key is the date. Other fields include fiscal year, fiscal period, fiscal quarter, holiday, and things like that. There are many foreign key references to this table.

To update it, we have a script that adds a year and updates everything except the holidays. That we do manually with a Cold Fusion web page.

It is relelevent that the software for this db, redbrick, has date, time, and timestamp fields. In other words, a date field does not include the time component.

Thanks, I managed to pull off the sql thing cfSearching mentioned. What is the best way to update this calendar table?

> how to do this purely in sql

In ms sql you could use a table valued udf. The udf could generate the dates and insert them into a table variable that would be used in your join. The basic logic is the same. Create two variables: @startDate and @todaysDate. Then loop while the start date is <= today's date. Within each loop insert the current date into the table variable, and increment the start date

http://msdn.microsoft.com/en-us/library/ms191165.aspx

while @startDate <= @today
begin
insert into @dateTable (theDate) values (@startDate)

set @startDate = dateAdd(d, 1, @startDate)
end

Though personally I prefer using using a permanent calendar table. I use that table in my selects rather than generating a table of dates each time.

Thats pretty clever stuff thanks Dan Bracuk . I would like to test it in the sql server so could someone show me how to do this purely in sql (with arbitary startDate)

select date2
from sometables
right join
(<cfset thedate = startdate>
<cfloop condition = "thedate lt now()>
select distinct #thedate# date2
from some_small_table
union
<cfset thedate = dateadd("d", 1, thedate)>
</cfloop>
select distinct #thedate# date2
from some_small_table

) sq on something = date2
where somefield is null