The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Sql server business day..

How can I determine a given business day in sql server? For example, what is the 5th business day in October of 2005 or even november of 2006?

I am using SQL Server and this system deals with USA calendar in terms of holidays. The application deals with financial industry. 

Do I have to create a BusinessDays table and list all the financial holidays in that table and use it as a lookup table?
data
Sunday, March 27, 2005
 
 
I would say that you need a lookup table. After all, what is a business day to one company may not be a business day to another. I seriously doubt that SQL Server would be able to distinguish it for you.
matt
Sunday, March 27, 2005
 
 
>Do I have to create a BusinessDays table and list all the financial holidays in that table and use it as a lookup table?

That is substantially what we do. If the stock market is closed, so is our business. If it is a federal holiday, sometimes our business is closed too.

For example, the past Friday is called "Good Friday." The stock market is closed, but it isn't a federal holiday. Since it is the Friday before Easter, and Easter is a moveable holiday (the Sunday after the first full moon on or after the day of the spring equinox), you will have to have a rather complicated procedure to determine when it happens each year.

A past employer of mine was closed for all the Christian, Jewish and federal holidays. It gets complicated when you mix lunar and solar calendars.
Peter
Sunday, March 27, 2005
 
 
Agreed.  A lookup table is the way to go.  I created one with an assortment of user defined functions "GetNextBusinessDay", "isBusinessDay", "GetNumberOfBusinessDays", etc. to make it easy to use.  "Eventually" I will create an interface so our secretary can update it.
zigzag Send private email
Monday, March 28, 2005
 
 
Vote for a lookup table too. Every few year there is a day when you government switch the day in order to support a longer weekend (eg. If thu is a holiday, they make fri a holiday as well, and next saturday a working day). with a lookup table it's easy.

Monday, March 28, 2005
 
 
i agree with the lookup table, and i recommend adding a category column(s) to your table also.  we had a business calendar based on the nyse calendar.  then we added fixed income trading.  surprise - different calendar.  gets worse with international trading added in.  so on good friday, some apps are on holiday and some apps are active.  your table may need to be able to handle those variations.
bw
Monday, March 28, 2005
 
 
Check out www.sqlservercentral.com. It's on my daily reading list - it's a really great site for the DBA/DBDeveloper.

Check out their scripts section. I'm betting that you can either find a script of a UDF to do exactly what you are looking for. If not, post to the forum and someone will more than likely have a good answer for you.
anomalous
Monday, March 28, 2005
 
 
Once upon a time, I was seriously considering making a joke calendar, with a name like "I can't work because it is..." If you take a look at all the national and religious holidays during the year, there are probably 10 days in a year that are not celebrated as some holiday somewhere. And I'm not so sure about those 10 days either. Its always "Saint Somebody's day."

The idea came about when staring at a slick from UPS one month that listed the days and countries that delivery would be affected.

Of course, if your company works on a 4-4-5 or 5-4-4 fiscal calendar, you are already bald from tearing your hair out trying to figure out what evil munchkin came up with this crazy stuff.
Peter
Monday, March 28, 2005
 
 
==> If you take a look at all the national and religious holidays during the year, there are probably 10 days in a year that are not celebrated as some holiday somewhere.

And for those remaining 10 days, start taking sick leave. And when that runs out, start burning accrued vacation time. By the time you're done, you've got the whole year covered!
Sgt.Sausage
Tuesday, March 29, 2005
 
 
That calendar was already invented by the French.
Steve Monk
Wednesday, March 30, 2005
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz