I am building a timesheet application where the app will created a "WORKED" record for all weekdays and skip the weekends. I am doing this in ASP.NET. I know I can use the calender control to identify the weekend and week days but I want to have it in a stored procedure so I can create the records at beganning of each month in a schedule.

is there a way to do it in the database? How can I identify weekend days in a given month?

Any help would be greatly appreciated.

check system function datepart in BOL.

select datepart(dw, getdate())
returns today's weekday.

Hi,

Have you considered using a look-up calendar table that could store all non working days and public holidays?  I have used one in a similar application and it proved useful when someone decided they would charge different rates to customers for having employees work weekends and public holidays.  This can be extended to support user specific holidays.

Best regards,

Andrew McKeon

I have written a function to check if a date value is a working day or not (used for an employee leave application, working out days absent, so should be similar).  The function returns 1 if the date is a weekend, 2 if it is a Bank Holiday (see below) and 0 if it is a working day.  It can easily be altered to account for alternative business rules, and works for any setting of @@datefirst.

create function dbo.WorkingDay (@InputDate datetime)
returns tinyint
as
begin

declare @Sunday tinyint, @Saturday tinyint, @Year smallint

select @Year = datepart(yy,@InputDate)

select @Sunday = 8 - @@datefirst
select @Saturday = ( (@Sunday+5) % 7) + 1

if (datepart(dw,@InputDate) = @Sunday) or (datepart(dw,@InputDate) = @Saturday)
 return(1)

if @InputDate in (select * from dbo.BankHolidays(@Year))
 return(2)

return(0)
end
go

The function BankHolidays returns a table with all Bank Holiday dates for the given year.  Bank Holidays are UK public holidays - if anyone wants the script to calculate it let me know and I'll post.  To calculate Good Friday and Easter Monday holidays, it uses a function to calculate Easter between 1900 and 2099 using the Carter algorithm, which I assume is the same for US.

Thank you Paul, I will try this function and let you know how it worked out.

Tarique

Paul,

In your function

select @Sunday = 8 - @@datefirst

this gives me a 1

select @@datefirst

give me a 7

What is this supposed to do?

Thanks for your help

@@datefirst returns the current setting for first day of the week, as set by SET DATEFIRST.  Values are 1 = Monday and so on through to 7 = Sunday.  You have first day of the week set as Sunday.

This means that DATEPART(dw,[date_of_a_sunday]) would always return 1 (meaning that it is the first day of the week).  If your first day of the week was set as Monday (i.e. @@datefirst = 1) then DATEPART(dw,[date_of_a_sunday]) would return 7.

So:

select @Sunday = 8 - @@datefirst

sets the variable @Sunday to what DATEPART will return if the input date is a Sunday, based on your current @@datefirst setting.  The @Sunday variable is then used within the function to compare against the DATEPART return to identify a Sunday.  This ensures the function works regardless of what it is set at (i.e. if you had Wednesday set as the first day of the week, the function would still correctly identify Saturdays and Sundays).

The next line works out what day Saturday would be (using modulo arithmetic), for the same purpose.

Hope that makes sense!

Paul,

Thanks. That makes sense. I did some tests based on what you said and it seems to be working. Thank you very much.

Paul,

In your earlier posts you mentioned that you had a function to work out UK bank holidays. Any chance of you posting this function or emailing me with it so that I can see how you achieved this as I ma trying to do something similiar.

Thanks for your help

Chris

Here you go, two functions - the comments should give you enough info to use them, or use the concept in another application.  The first function returns a datetime corresponding to the date of Easter Sunday for the year input (this is required for the second function).   The second function returns a table of datetimes with all bank holiday dates for the current year.

The main difficulty is Easter, but if you don't mind being restricted to 1900-2099 (which should be ample for most applications) then the Carter algorithm below should help.

if exists (select * from sysobjects where [name] = 'Easter' and [type] = 'FN')
 drop function dbo.Easter
go

create function Easter (@Y smallint)
returns datetime
as
begin
/*
 EASTER SUNDAY FUNCTION
Calculates date of Easter Sunday based on the Carter algorithm.
This one works for 1900-2099.  More complex algorithms exist,
but are deemed unnecessary for this application (for efficiency).
Sourced from National Maritime Museum - http://www.nmm.ac.uk/
Algorithm instructions from the Museum are included as comments.

*/

declare @D tinyint, @E tinyint, @Q tinyint
declare @ret datetime

-- Calculate D = 225 - 11(Y MOD 19)
select @D = 225 - (11 * (@Y % 19))

-- If D is greater than 50 then subtract multiples of 30 until the
-- resulting new value of D is less than 51
while @D > 50
 select @D = @D - 30

-- If D is greater than 48 subtract 1 from it
if @D > 48
 select @D = @D - 1

-- Calculate E = (Y + (Y/4) + D + 1) MOD 7. (NB Integer part of (Y/4))
select @E = (@Y + floor(@Y/4) + @D + 1) % 7

-- Calculate Q = D + 7 - E
select @Q = @D + 7 - @E

-- If Q is less than 32 then Easter is in March.  If Q is greater than 31,
-- then Q-31 is its date in April.
if @Q < 32
 select @ret = convert(datetime,'03/'+convert(varchar,@Q)+'/'+convert(varchar,@Y))
else
 select @ret = convert(datetime,'04/'+convert(varchar,(@Q-31))+'/'+convert(varchar,@Y))

return(@ret)
end
go

-- Function to calculate bank holidays

if exists (select * from sysobjects where [name] = 'BankHolidays' and [type] = 'TF')
 drop function dbo.BankHolidays
go

create function dbo.BankHolidays (@YearIn smallint)
returns @ret table (BHDate datetime)
as
begin
/*

ENGLAND AND WALES BANK HOLIDAY DATES 1978 - 2099
 AUTOMATIC CALCULATION

Restricted by Easter Sunday algorithm to 1900 - 2099
Can be set to work for any dates between 1900 - 2099 but:
- Current Bank Holidays were introduced in 1971 by the
  Banking and Financial Dealings Act 1971, so date before
  then will be inaccurate
- New Year's Day was introduced in 1974*
- Early May Bank Holiday was introduced in 1978*
- The script therefore does not account for bank holiday dates
  introduced by the Bank Holiday Act of 1871 which ran until 1970.
*not included in Act so declared by Royal Proclamation each year

Developed by Paul Cresham
© 2004 Orchard Information Systems Limited

*/

declare @Holiday datetime
declare @Saturday tinyint, @Sunday tinyint, @Monday tinyint

select @Sunday = 8 - @@datefirst
select @Saturday = ( (@Sunday+5) % 7) + 1
select @Monday = ( (@Sunday) % 7) + 1

-- No 1: New Year's Day
-- 1 January or next weekday if it falls on a Saturday or Sunday
select @Holiday = convert(datetime,'01/01/'+convert(varchar,@YearIn))

while (datepart(dw,@Holiday) = @Saturday) Or (datepart(dw,@Holiday) = @Sunday)
 select @Holiday = dateadd(day, 1, @Holiday)

insert @ret values (@Holiday)

-- No 2: Good Friday
-- 2 days before Easter Sunday

select @Holiday = dbo.Easter(@YearIn)
select @Holiday = dateadd(day,-2,@Holiday)

insert @ret values (@Holiday)

-- No 3: Easter Monday
-- 1 day after Easter Sunday (or 3 days after date currently held for Good Friday)
select @Holiday = dateadd(day,3,@Holiday)

insert @ret values (@Holiday)

-- No 4: May Day
-- 1st Monday in May

select @Holiday = convert(datetime,'05/01/'+convert(varchar,@YearIn))

while datepart(dw,@Holiday) != @Monday
 select @Holiday = dateadd(day,1,@Holiday)

insert @ret values (@Holiday)

-- No 5: Spring Bank Holiday
-- Last Monday in May

select @Holiday = convert(datetime,'05/31/'+convert(varchar,@YearIn))

while datepart(dw,@Holiday) != @Monday
 select @Holiday = dateadd(day,-1,@Holiday)

insert @ret values (@Holiday)

-- No 6: Late Summer Bank Holiday
-- Last Monday in August

select @Holiday = convert(datetime,'08/31/'+convert(varchar,@YearIn))

while datepart(dw,@Holiday) != @Monday
 select @Holiday = dateadd(day,-1,@Holiday)

insert @ret values (@Holiday)

-- No 7: Christmas Day
-- 25 December, or next Monday in lieu if falls on Saturday or Sunday

select @Holiday = convert(datetime,'12/25/'+convert(varchar,@YearIn))

while (datepart(dw,@Holiday) = @Saturday) Or (datepart(dw,@Holiday) = @Sunday)
 select @Holiday = dateadd(day,1,@Holiday)

insert @ret values (@Holiday)

-- No 8: Boxing Day
-- 26 December, or next Monday in lieu if this falls on a Saturday or
-- Tuesday if it falls on a Sunday

select @Holiday = dateadd(day,1,@Holiday)

while (datepart(dw,@Holiday) = @Saturday) Or (datepart(dw,@Holiday) = @Sunday)
 select @Holiday = dateadd(day,1,@Holiday)

insert @ret values (@Holiday)

return

end
go

___________________________________________________________
This post is supplied "as is" and confers no rights and is without any warranties, express or implied.