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