Query to populate date drop down

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query to populate date drop down

Author

Topic

Gsuttie
Starting Member

14 Posts

Posted - 2002-01-09 : 13:18:27

Hi there

I need to populate a drop down of dates for the next 30 days from say todays date. I need this to not take into account saturday or sunday.
Possibly need it to be configurable to miss out certain holidays(i.e) say mon the 1st happened to be a bank holiday.

Any suggestions or help would be greatly appreciated.

Regards
Gregor

Greg

ToddV
Posting Yak Master

218 Posts

Posted - 2002-01-09 : 13:36:34

Probably the best way will be to make a table to hold all the valid dates, but alternatively you could just make a table with holidays and then do something like this.

SELECT TOP 30 DATEADD(dd,Tally,CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),102)))
FROM #TALLY A
LEFT JOIN Holidays B
ON B.[DATE] = CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),102))
WHERE DATEPART(dw,DATEADD(dd,Tally,CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),102)))) NOT IN (1,7)
AND B.DateIS NULL
ORDER BY DATEADD(dd,Tally,CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),102)))

For information about table called tally search this site for tally or sequence.

mfemenel
Professor Frink

1421 Posts

Posted - 2002-01-09 : 15:13:02

To handle the holidays I'd suggest a lookup table which you could use to omit the date if it matches a holiday.

Mike
"A program is a device used to convert data into error messages."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources