Calculating Buisness Week

[Thomas]

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

[Thomas]

"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

[Thomas]

"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

[Michael Levy]

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

[Jim Schepflin]

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

[Dave White]

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
>