Date issue
-
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.
-
Hi,
I am working on a call accounting software, it would be of great help if you could assist me to verify a particular date for a public holiday or not.
I am trying so hard but could not come up with a positive solution.
Thanks for your help
Regards
Gayathri
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply