Calculating Buisness Week


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Calculating Buisness Week

  1. #1
    Thomas Guest

    Calculating Buisness Week


    Does anyone have the SQL to calculate the start and end dates of the buisness
    weeks for any given month? I would like to automate every process in my
    queries without having to enter dates. The buisness week runs from Sunday
    though Saturday starting on the first of the month and ending on the last
    day of the month. Any help is greatly appreciated.

    Thanks,

    Thomas

  2. #2
    Thomas Guest

    Re: Calculating Buisness Week


    "Thomas" <tomsasnet@afninet.com> wrote:
    >
    >Does anyone have the SQL to calculate the start and end dates of the buisness
    >weeks for any given month? I would like to automate every process in my
    >queries without having to enter dates. The buisness week runs from Sunday
    >though Saturday starting on the first of the month and ending on the last
    >day of the month. Any help is greatly appreciated.
    >
    >Thanks,
    >
    >Thomas



    Got it. Here it is if anyone else may need it.

    SELECT
    CONVERT(DATETIME,CONVERT(VARCHAR(2),DATEPART(mm,GETDATE())) + '/'+ '1'+ '/'+
    CONVERT(VARCHAR(4),DATEPART(yy,GETDATE()))) AS FOM,
    DATEADD(DAY, -1, DATEADD (MONTH, 1, CONVERT(DATETIME,CONVERT(VARCHAR(2),DATEPART(mm,GETDATE()))
    + '/'+ '1'+ '/'+ CONVERT(VARCHAR(4),DATEPART(yy,GETDATE()))))) AS LOM,
    CONVERT(DATETIME,CONVERT(VARCHAR(2),DATEPART(mm,GETDATE())) + '/'+ CONVERT(VARCHAR,8
    - (DATEPART(DW,CONVERT(DATETIME,CONVERT(VARCHAR(2),DATEPART(mm,GETDATE()))
    + '/'+ '1'+ '/'+ CONVERT(VARCHAR(4),DATEPART(yy,GETDATE()))))))+ '/'+ CONVERT(VARCHAR(4),DATEPART(yy,GETDATE())))
    AS FOM1,
    DATEADD(DAY, 7, CONVERT(DATETIME,CONVERT(VARCHAR(2),DATEPART(mm,GETDATE()))
    + '/'+ CONVERT(VARCHAR,8 - (DATEPART(DW,CONVERT(DATETIME,CONVERT(VARCHAR(2),DATEPART(mm,GETDATE()))
    + '/'+ '1'+ '/'+ CONVERT(VARCHAR(4),DATEPART(yy,GETDATE()))))))+ '/'+ CONVERT(VARCHAR(4),DATEPART(yy,GETDATE()))))
    AS SW,
    DATEADD(DAY, 14, CONVERT(DATETIME,CONVERT(VARCHAR(2),DATEPART(mm,GETDATE()))
    + '/'+ CONVERT(VARCHAR,8 - (DATEPART(DW,CONVERT(DATETIME,CONVERT(VARCHAR(2),DATEPART(mm,GETDATE()))
    + '/'+ '1'+ '/'+ CONVERT(VARCHAR(4),DATEPART(yy,GETDATE()))))))+ '/'+ CONVERT(VARCHAR(4),DATEPART(yy,GETDATE()))))
    AS TW,
    DATEADD(DAY, 21, CONVERT(DATETIME,CONVERT(VARCHAR(2),DATEPART(mm,GETDATE()))
    + '/'+ CONVERT(VARCHAR,8 - (DATEPART(DW,CONVERT(DATETIME,CONVERT(VARCHAR(2),DATEPART(mm,GETDATE()))
    + '/'+ '1'+ '/'+ CONVERT(VARCHAR(4),DATEPART(yy,GETDATE()))))))+ '/'+ CONVERT(VARCHAR(4),DATEPART(yy,GETDATE()))))
    AS FW


  3. #3
    Thomas Guest

    Re: Calculating Buisness Week


    "Thomas" <tomsasnet@afninet.com> wrote:
    >
    >"Thomas" <tomsasnet@afninet.com> wrote:
    >>
    >>Does anyone have the SQL to calculate the start and end dates of the buisness
    >>weeks for any given month? I would like to automate every process in my
    >>queries without having to enter dates. The buisness week runs from Sunday
    >>though Saturday starting on the first of the month and ending on the last
    >>day of the month. Any help is greatly appreciated.
    >>
    >>Thanks,
    >>
    >>Thomas

    >
    >Made some corrections to this one.
    >Here it is if anyone else may need it.
    >

    SELECT
    CONVERT(DATETIME,CONVERT(VARCHAR(2),DATEPART(mm,GETDATE())) + '/'+ '1'+ '/'+
    CONVERT(VARCHAR(4),DATEPART(yy,GETDATE()))) AS FOM,
    CONVERT(DATETIME,CONVERT(VARCHAR(2),DATEPART(mm,GETDATE())) + '/'+ CONVERT(VARCHAR,8
    - (DATEPART(DW,CONVERT(DATETIME,CONVERT(VARCHAR(2),DATEPART(mm,GETDATE()))
    + '/'+ '1'+ '/'+ CONVERT(VARCHAR(4),DATEPART(yy,GETDATE()))))))+ '/'+ CONVERT(VARCHAR(4),DATEPART(yy,GETDATE())))
    AS FW,
    DATEADD(Day, 1, CONVERT(DATETIME,CONVERT(VARCHAR(2),DATEPART(mm,GETDATE()))
    + '/'+ CONVERT(VARCHAR,8 - (DATEPART(DW,CONVERT(DATETIME,CONVERT(VARCHAR(2),DATEPART(mm,GETDATE()))
    + '/'+ '1'+ '/'+ CONVERT(VARCHAR(4),DATEPART(yy,GETDATE()))))))+ '/'+ CONVERT(VARCHAR(4),DATEPART(yy,GETDATE()))))
    AS FWPlus1,
    DATEADD(DAY, 7, CONVERT(DATETIME,CONVERT(VARCHAR(2),DATEPART(mm,GETDATE()))
    + '/'+ CONVERT(VARCHAR,8 - (DATEPART(DW,CONVERT(DATETIME,CONVERT(VARCHAR(2),DATEPART(mm,GETDATE()))
    + '/'+ '1'+ '/'+ CONVERT(VARCHAR(4),DATEPART(yy,GETDATE()))))))+ '/'+ CONVERT(VARCHAR(4),DATEPART(yy,GETDATE()))))
    AS SW,
    DATEADD(DAY, 8, CONVERT(DATETIME,CONVERT(VARCHAR(2),DATEPART(mm,GETDATE()))
    + '/'+ CONVERT(VARCHAR,8 - (DATEPART(DW,CONVERT(DATETIME,CONVERT(VARCHAR(2),DATEPART(mm,GETDATE()))
    + '/'+ '1'+ '/'+ CONVERT(VARCHAR(4),DATEPART(yy,GETDATE()))))))+ '/'+ CONVERT(VARCHAR(4),DATEPART(yy,GETDATE()))))
    AS SWPlus1,
    DATEADD(DAY, 14, CONVERT(DATETIME,CONVERT(VARCHAR(2),DATEPART(mm,GETDATE()))
    + '/'+ CONVERT(VARCHAR,8 - (DATEPART(DW,CONVERT(DATETIME,CONVERT(VARCHAR(2),DATEPART(mm,GETDATE()))
    + '/'+ '1'+ '/'+ CONVERT(VARCHAR(4),DATEPART(yy,GETDATE()))))))+ '/'+ CONVERT(VARCHAR(4),DATEPART(yy,GETDATE()))))
    AS TW,
    DATEADD(DAY, 15, CONVERT(DATETIME,CONVERT(VARCHAR(2),DATEPART(mm,GETDATE()))
    + '/'+ CONVERT(VARCHAR,8 - (DATEPART(DW,CONVERT(DATETIME,CONVERT(VARCHAR(2),DATEPART(mm,GETDATE()))
    + '/'+ '1'+ '/'+ CONVERT(VARCHAR(4),DATEPART(yy,GETDATE()))))))+ '/'+ CONVERT(VARCHAR(4),DATEPART(yy,GETDATE()))))
    AS TWPlus1,
    DATEADD(DAY, 21, CONVERT(DATETIME,CONVERT(VARCHAR(2),DATEPART(mm,GETDATE()))
    + '/'+ CONVERT(VARCHAR,8 - (DATEPART(DW,CONVERT(DATETIME,CONVERT(VARCHAR(2),DATEPART(mm,GETDATE()))
    + '/'+ '1'+ '/'+ CONVERT(VARCHAR(4),DATEPART(yy,GETDATE()))))))+ '/'+ CONVERT(VARCHAR(4),DATEPART(yy,GETDATE()))))
    AS FthW,
    DATEADD(DAY, 22, CONVERT(DATETIME,CONVERT(VARCHAR(2),DATEPART(mm,GETDATE()))
    + '/'+ CONVERT(VARCHAR,8 - (DATEPART(DW,CONVERT(DATETIME,CONVERT(VARCHAR(2),DATEPART(mm,GETDATE()))
    + '/'+ '1'+ '/'+ CONVERT(VARCHAR(4),DATEPART(yy,GETDATE()))))))+ '/'+ CONVERT(VARCHAR(4),DATEPART(yy,GETDATE()))))
    AS FthWPlus1,
    DATEADD(DAY, -1, DATEADD (MONTH, 1, CONVERT(DATETIME,CONVERT(VARCHAR(2),DATEPART(mm,GETDATE()))
    + '/'+ '1'+ '/'+ CONVERT(VARCHAR(4),DATEPART(yy,GETDATE()))))) AS LOM

  4. #4
    Michael Levy Guest

    Re: Calculating Buisness Week

    This might be sensitive to the DATEFORMAT setting. Maybe you could change
    the calculations to be pure date manipulations instead of creating and
    converting strings:

    For instance, this is the first day of the current month:
    SELECT getdate() - datepart(day, getdate()) + 1

    This returns the first day of the current week, starting on Sunday:
    SELECT getdate() - datepart(weekday, getdate()) + 1

    -Mike
    --
    Michael Levy MCDBA, MCSD, MCT
    michaell@gasullivan.com



  5. #5
    Jim Schepflin Guest

    Re: Calculating Buisness Week


    As seen in some of the prior posts, the calculations start getting messy as
    you do more of them.
    One technique we have used to to make a Calendar table, with an entry for
    each day, and breakdowns for Day, Week, Month, etc on both a Fiscal and Calendar
    basis. This allows a simple join to this table to get the appropriate week/month/day,
    etc, may make your life easier, particuarlly if there is more need than on
    this one example.

    The calculation can be done in pretty much any programming language, isn't
    too hard.
    Jim
    "Thomas" <tomsasnet@afninet.com> wrote:
    >
    >Does anyone have the SQL to calculate the start and end dates of the buisness
    >weeks for any given month? I would like to automate every process in my
    >queries without having to enter dates. The buisness week runs from Sunday
    >though Saturday starting on the first of the month and ending on the last
    >day of the month. Any help is greatly appreciated.
    >
    >Thanks,
    >
    >Thomas



  6. #6
    Dave White Guest

    Re: Calculating Buisness Week


    I had to complete a similar task recently. What I did was use the Application(VBA)
    to insert records for everyday of the year(or a given date range) into a
    table. Using the "Weekday" function in VBA I was able to decide what was
    a weekday or weekend.

    Example:

    Dim start,current,end as date
    Dim flag as boolean

    start = me!startdate (control on form)
    end = me!enddate (control on form)
    current = start

    Do until current > end

    flag = weekday(current)
    Insert into tbl_dates (1,current,flag)

    current = current + 1

    loop

    My table had the following fields:

    ID(Integer),Date(Date),Weekday(Boolean)

    If I inserted todays date, it would look like the following.

    ID DATE WEEKDAY
    1 28/11/2000 -1 (True)

    I also created a custom Calender control so a user could set Holidays within
    the table, if weekday = 0 then it is either a Weekend or a company holiday.

    Hope this helps

    Dave.

    "Jim Schepflin" <jims@cepsystems.com> wrote:
    >
    >As seen in some of the prior posts, the calculations start getting messy

    as
    >you do more of them.
    >One technique we have used to to make a Calendar table, with an entry for
    >each day, and breakdowns for Day, Week, Month, etc on both a Fiscal and

    Calendar
    >basis. This allows a simple join to this table to get the appropriate week/month/day,
    >etc, may make your life easier, particuarlly if there is more need than

    on
    >this one example.
    >
    >The calculation can be done in pretty much any programming language, isn't
    >too hard.
    >Jim
    >"Thomas" <tomsasnet@afninet.com> wrote:
    >>
    >>Does anyone have the SQL to calculate the start and end dates of the buisness
    >>weeks for any given month? I would like to automate every process in my
    >>queries without having to enter dates. The buisness week runs from Sunday
    >>though Saturday starting on the first of the month and ending on the last
    >>day of the month. Any help is greatly appreciated.
    >>
    >>Thanks,
    >>
    >>Thomas

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center