Does date fall on a work day or on weekend 2

[barrattp]

Hello.
1.I have to identify if a date falls within a work week (Monday to Friday) or falls on a week end.

2. I also will need to set up a proceedure to identify Public Holidays. The database that I am using does not have a calender table. I was going to hard code an If statement with the dates included until a calender table is established.

Any ideas for either of these problems.

 

[mbarron]

Try these:

1.
If dayofweek({your.date.field}) in 2 to 5 then "weekday" else "weekend"

2.
if {your.date.field} in [date(2003,12,25),date(2003,7,4)] then holiday else it's not.

Mike

 

[synapsevampire]

My standard suggestion here is to take a professional approach to this problem by creating a Periods table. This solves a number of problems for decision support, inclusive of the common Holdays concern.

The table would have all dates, and include flags for Holiday, weekend, work week, etc.

I have scripts that generate a Period table for SQL Server that I can share, and have in the past, so you might just do a search of Tek-Tips.

Otherwise, here's some Crystal whitepapers for hacking it in Crystal:

http://support.crystaldecisions.com/communityCS/TechnicalPapers/cr_business_days_hours.zip.asp

http://support.crystaldecisions.com/communityCS/TechnicalPapers/docs_reportdesign.pdf

-k