I wrote the following function to find the start of week date for a given date and a given start day of week.
For example:
If the day passed is Saturday, 2005-03-19, and Sunday is the start of the week, it returns: 2005-03-13 00:00:00.000
If the day passed is Monday, 2005-03-14, and Sunday is the start of the week, it returns: 2005-03-13 00:00:00.000
If the day passed is Monday, 2005-03-14, and Monday is the start of the week, it returns: 2005-03-14 00:00:00.000
Does anyone have a simpler algorithim for start of week that they care to post?
Edit (2006/4/15):
Posted a companion function F_END_OF_WEEK, on this topic:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64760
There are other Start of Time Period Functions posted here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755
There are other End Date of Time Period Functions here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64759
create function dbo.F_START_OF_WEEK
(
@DATEdatetime,
-- Sun = 1, Mon = 2, Tue = 3, Wed = 4
-- Thu = 5, Fri = 6, Sat = 7
-- Default to Sunday
@WEEK_START_DAYint= 1
)
/*
Find the fisrt date on or before @DATE that matches
day of week of @WEEK_START_DAY.
*/
returnsdatetime
as
begin
declare @START_OF_WEEK_DATEdatetime
declare @FIRST_BOWdatetime
-- Check for valid day of week
if @WEEK_START_DAY between 1 and 7
begin
-- Find first day on or after 1753/1/1 (-53690)
-- matching day of week of @WEEK_START_DAY
-- 1753/1/1 is earliest possible SQL Server date.
select @FIRST_BOW = convert(datetime,-53690+((@WEEK_START_DAY+5)%7))
-- Verify beginning of week not before 1753/1/1
if @DATE >= @FIRST_BOW
begin
select @START_OF_WEEK_DATE =
dateadd(dd,(datediff(dd,@FIRST_BOW,@DATE)/7)*7,@FIRST_BOW)
end
end
return @START_OF_WEEK_DATE
end
go
-- Sample function calls
select dbo.F_START_OF_WEEK(getdate(),default) -- Returns Date for Sunday
select dbo.F_START_OF_WEEK(getdate(),1)-- Returns Date for Sunday
select dbo.F_START_OF_WEEK(getdate(),2)-- Returns Date for Monday
select dbo.F_START_OF_WEEK(getdate(),3)-- Returns Date for Tuesday
select dbo.F_START_OF_WEEK(getdate(),4)-- Returns Date for Wednesday
select dbo.F_START_OF_WEEK(getdate(),5)-- Returns Date for Thursday
select dbo.F_START_OF_WEEK(getdate(),6)-- Returns Date for Friday
select dbo.F_START_OF_WEEK(getdate(),7)-- Returns Date for Saturday
I thought I would also post an alternate way of doing the Start of Week instead of using the F_START_OF_WEEK function. These queries demo doing the Start of Week inline in a query, and use a similar algorithm to find the start of week, but the start day of week is hard coded.
I posted two versions. The first version is simpler, but it has a minor flaw that returns a false result if the start of week would be before 1753/1/1. For the vast majority of applications this would not be a problem. In the second, the algorithm is modified slightly to cause it to overflow if you pick a date that would result in a start of week before 1753/1/1. Note that the F_START_OF_WEEK function returns a NULL in this situation.
The demo queries use the F_TABLE_NUMBER_RANGE that is posted in another thread in order to generate dates to demonstrated the results:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
-- First demo query for Start of Week
-- Returns bad result if the start of week would be before 1753/1/1
select
DATE,
Sun = dateadd(dd,(datediff(dd,-53684,a.DATE)/7)*7,-53684),
Mon = dateadd(dd,(datediff(dd,-53690,a.DATE)/7)*7,-53690),
Tue = dateadd(dd,(datediff(dd,-53689,a.DATE)/7)*7,-53689),
Wed = dateadd(dd,(datediff(dd,-53688,a.DATE)/7)*7,-53688),
Thu = dateadd(dd,(datediff(dd,-53687,a.DATE)/7)*7,-53687),
Fri = dateadd(dd,(datediff(dd,-53686,a.DATE)/7)*7,-53686),
Sat = dateadd(dd,(datediff(dd,-53685,a.DATE)/7)*7,-53685)
from
(
select
DATE = convert(datetime,number)
from
F_TABLE_NUMBER_RANGE(36524,40000)
) a
-- Second demo query for Start of Week
-- Modified to cause an error instead of returning a bad date
-- if the start of week would be before 1753/1/1
select
DATE,
Sun = dateadd(dd,((datediff(dd,-53684,a.DATE+7)/7)*7)-7,-53684),
Mon = dateadd(dd,((datediff(dd,-53690,a.DATE+7)/7)*7)-7,-53690),
Tue = dateadd(dd,((datediff(dd,-53689,a.DATE+7)/7)*7)-7,-53689),
Wed = dateadd(dd,((datediff(dd,-53688,a.DATE+7)/7)*7)-7,-53688),
Thu = dateadd(dd,((datediff(dd,-53687,a.DATE+7)/7)*7)-7,-53687),
Fri = dateadd(dd,((datediff(dd,-53686,a.DATE+7)/7)*7)-7,-53686),
Sat = dateadd(dd,((datediff(dd,-53685,a.DATE+7)/7)*7)-7,-53685)
from
(
select
DATE = convert(datetime,number)
from
F_TABLE_NUMBER_RANGE(36524,40000)
) a
I need a Select sentence that return me the first week of the month for a given week.
For example If I have week number 12 (Begins 2015/03/16 and Ends 2015/03/22) I need that returns 9, I mean Week number 9 wich is the first week of march (having in mind @@DATEFIRST).
I only need give a week number of the year and then returns the week number of the first week of that month.
Function F_ISO_YEAR_WEEK_DAY_OF_WEEK returns the ISO 8601 Year Week Day of Week in format YYYY-W01-D for the date passed. W01 represents the week of the year from W01 through W53, and D represents the day of the week with 1 = Monday through 7 = Sunday.
The first week of each year starts on the first Monday on or before January 4 of that year, so that the year begins from December 28 of the prior year through January 4 of the current year.
This code creates the function and demos it for the first day, first date+60, and first date+364 for each ISO week/year from 1990 to 2030.
drop function dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK GO create function dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK ( @Datedatetime ) returnsvarchar(10) as /* Function F_ISO_YEAR_WEEK_DAY_OF_WEEK returns the ISO 8601 Year Week Day of Week in format YYYY-W01-D for the date passed. */ begin
declare @YearWeekDayOfWeekvarchar(10)
Select --Format to form YYYY-W01-D @YearWeekDayOfWeek = convert(varchar(4),year(dateadd(dd,7,a.YearStart)))+'-W'+ right('00'+convert(varchar(2),(datediff(dd,a.YearStart,@Date)/7)+1),2) + '-'+convert(varchar(1),(datediff(dd,a.YearStart,@Date)%7)+1) from ( select YearStart = -- Case finds start of year case whenNextYrStart <= @date thenNextYrStart whenCurrYrStart <= @date thenCurrYrStart elsePriorYrStart end from ( select -- First day of first week of prior year PriorYrStart = dateadd(dd,(datediff(dd,-53690,dateadd(yy,-1,aaa.Jan4))/7)*7,-53690), -- First day of first week of current year CurrYrStart = dateadd(dd,(datediff(dd,-53690,aaa.Jan4)/7)*7,-53690), -- First day of first week of next year NextYrStart = dateadd(dd,(datediff(dd,-53690,dateadd(yy,1,aaa.Jan4))/7)*7,-53690) from ( select --Find Jan 4 for the year of the input date Jan4= dateadd(dd,3,dateadd(yy,datediff(yy,0,@date),0)) ) aaa ) aa ) a
return @YearWeekDayOfWeek
end go
-- Execute function on first day, first day+60, -- and first day+364 for years from 1990 to 2030.
select DT= convert(varchar(10),DT,121), YR_START_DT = dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK(a.DT), YR_START_DT_60 = dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK(a.DT+60), YR_START_DT_365 = dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK(a.DT+364) from ( select DT = getdate()union all select DT = convert(datetime,'1990/01/01') union all select DT = convert(datetime,'1990/12/31') union all select DT = convert(datetime,'1991/12/30') union all select DT = convert(datetime,'1993/01/04') union all select DT = convert(datetime,'1994/01/03') union all select DT = convert(datetime,'1995/01/02') union all select DT = convert(datetime,'1996/01/01') union all select DT = convert(datetime,'1996/12/30') union all select DT = convert(datetime,'1997/12/29') union all select DT = convert(datetime,'1999/01/04') union all select DT = convert(datetime,'2000/01/03') union all select DT = convert(datetime,'2001/01/01') union all select DT = convert(datetime,'2001/12/31') union all select DT = convert(datetime,'2002/12/30') union all select DT = convert(datetime,'2003/12/29') union all select DT = convert(datetime,'2005/01/03') union all select DT = convert(datetime,'2006/01/02') union all select DT = convert(datetime,'2007/01/01') union all select DT = convert(datetime,'2007/12/31') union all select DT = convert(datetime,'2008/12/29') union all select DT = convert(datetime,'2010/01/04') union all select DT = convert(datetime,'2011/01/03') union all select DT = convert(datetime,'2012/01/02') union all select DT = convert(datetime,'2012/12/31') union all select DT = convert(datetime,'2013/12/30') union all select DT = convert(datetime,'2014/12/29') union all select DT = convert(datetime,'2016/01/04') union all select DT = convert(datetime,'2017/01/02') union all select DT = convert(datetime,'2018/01/01') union all select DT = convert(datetime,'2018/12/31') union all select DT = convert(datetime,'2019/12/30') union all select DT = convert(datetime,'2021/01/04') union all select DT = convert(datetime,'2022/01/03') union all select DT = convert(datetime,'2023/01/02') union all select DT = convert(datetime,'2024/01/01') union all select DT = convert(datetime,'2024/12/30') union all select DT = convert(datetime,'2025/12/29') union all select DT = convert(datetime,'2027/01/04') union all select DT = convert(datetime,'2028/01/03') union all select DT = convert(datetime,'2029/01/01') union all select DT = convert(datetime,'2029/12/31') union all select DT = convert(datetime,'2030/12/30') ) a
So I need to select and average a whole bunch of data by week. Currently, I group by:
GROUP BY TO_CHAR(m.ENDTIME, 'IW')
and select using
SELECT ... TO_CHAR(m.ENDTIME, 'IW') Week
to summarize by week.
Since the first day of the year was a monday, it sets the weeks as monday-monday. I need to set the weeks to be Thursday 7pm to Thursday 7pm. How can this be achieved? Thanks.
This script creates a scalar function, F_END_OF_WEEK, that returns the end of week date for a passed date and a passed start day of week.
Parameter @DATE can be any valid datetime. Parameter @WEEK_START_DAY must be an integer in the range 1 through 7, with Sun = 1, Mon = 2, Tue = 3, Wed = 4, Thu = 5, Fri = 6, and Sat = 7.
This function is a companion to function F_START_OF_WEEK and has the same input parameters, @DATE and @WEEK_START_DAY. If they are called with the same input parameters, they will return the first and last day of the week.
Function F_END_OF_WEEK will return a null if the end of week day would be later than 9999-12-31.
The test code at the end of the script uses function F_START_OF_WEEK which can be found on this link: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307
The test code at the end of the script uses function F_TABLE_NUMBER_RANGE which can be found on this link: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
There are other Start of Time Period Functions posted here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755
There are other End Date of Time Period Functions here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64759
/* Function created by this script: dbo.F_END_OF_WEEK( @DATE, @WEEK_START_DAY ) */ if objectproperty(object_id('dbo.F_END_OF_WEEK'),'IsScalarFunction') = 1 begin drop function dbo.F_END_OF_WEEK end go create function dbo.F_END_OF_WEEK ( @DATEdatetime, -- Sun = 1, Mon = 2, Tue = 3, Wed = 4 -- Thu = 5, Fri = 6, Sat = 7 -- Default to Sunday @WEEK_START_DAYint= 1 ) /* Function: F_END_OF_WEEK Finds start of last day of week at 00:00:00.000 for input datetime, @DAY, for a week that started on the day of week of @WEEK_START_DAY. Returns a null if the end of week date would be after 9999-12-31. */ returnsdatetime as begin declare @END_OF_WEEK_DATEdatetime declare @FIRST_BOWdatetime declare @LAST_EOWdatetime
-- Check for valid day of week, and return null if invalid if not @WEEK_START_DAY between 1 and 7 return null
-- Find the last end of week for the passed day of week select @LAST_EOW =convert(datetime,2958457+((@WEEK_START_DAY+6)%7))
-- Return null if end of week for date passed is after 9999-12-31 if @DATE > @LAST_EOW return null
-- Find the first valid beginning of week for the date passed. select @FIRST_BOW = convert(datetime,-53690+((@WEEK_START_DAY+5)%7))
-- If date is before the first beginning of week for the passed day of week -- return the day before the first beginning of week if @DATE < @FIRST_BOW begin set @END_OF_WEEK_DATE = dateadd(dd,-1,@FIRST_BOW) return @END_OF_WEEK_DATE end
-- Find end of week for the normal case as 6 days after the beginning of week select @END_OF_WEEK_DATE = dateadd(dd,((datediff(dd,@FIRST_BOW,@DATE)/7)*7)+6,@FIRST_BOW)
return @END_OF_WEEK_DATE
end go
/* Start of test script */
select [DATE] = convert(varchar(10),a.DT,121), --WEEK_START_DAY = convert(varchar(2),b.number), FUNC_DW = case b.number when 1 then 'Sun' when 2 then 'Mon' when 3 then 'Tue' when 4 then 'Wed' when 5 then 'Thu' when 6 then 'Fri' when 7 then 'Sat' else null end, START_OF_WEEK = convert(varchar(10),dbo.F_START_OF_WEEK( a.DT, b.number ),121), START_DW = left(datename(dw,dbo.F_START_OF_WEEK( a.DT, b.number )),3), END_OF_WEEK = convert(varchar(10),dbo.F_END_OF_WEEK( a.DT, b.number ),121), EOW_DW = left(datename(dw,dbo.F_END_OF_WEEK( a.DT, b.number )),3) from ( -- Get dates from end of datetime range select DT = dateadd(dd,a1.number,'9999-12-25') from dbo.F_TABLE_NUMBER_RANGE(0,6) a1 union all -- Get some normal dates, +/- 10 day from current date select DT = dateadd(dd,a3.number,getdate()) from dbo.F_TABLE_NUMBER_RANGE(-10,10) a3 union all -- Get dates from beginning of datetime range select DT = dateadd(dd,a2.number,'17530101') from dbo.F_TABLE_NUMBER_RANGE(0,6) a2 ) a cross join dbo.F_TABLE_NUMBER_RANGE(1,7) b order by a.dt, b.number
This function returns the ISO 8601 week of the year for the date passed. The first week of each year starts on the first Monday on or before January 4 of that year, so that the year begins from December 28 of the prior year through January 4 of the current year.
This code creates the function and demos it for the first day of each ISO week/year from 1990 to 2030.
drop function dbo.F_ISO_WEEK_OF_YEAR go create function dbo.F_ISO_WEEK_OF_YEAR ( @Datedatetime ) returnsint as /* Function F_ISO_WEEK_OF_YEAR returns the ISO 8601 week of the year for the date passed. */ begin
declare @WeekOfYearint
select -- Compute week of year as (days since start of year/7)+1 -- Division by 7 gives whole weeks since start of year. -- Adding 1 starts week number at 1, instead of zero. @WeekOfYear = (datediff(dd, -- Case finds start of year case whenNextYrStart <= @date thenNextYrStart whenCurrYrStart <= @date thenCurrYrStart elsePriorYrStart end,@date)/7)+1 from ( select -- First day of first week of prior year PriorYrStart = dateadd(dd,(datediff(dd,-53690,dateadd(yy,-1,aa.Jan4))/7)*7,-53690), -- First day of first week of current year CurrYrStart = dateadd(dd,(datediff(dd,-53690,aa.Jan4)/7)*7,-53690), -- First day of first week of next year NextYrStart = dateadd(dd,(datediff(dd,-53690,dateadd(yy,1,aa.Jan4))/7)*7,-53690) from ( select --Find Jan 4 for the year of the input date Jan4= dateadd(dd,3,dateadd(yy,datediff(yy,0,@date),0)) ) aa ) a
return @WeekOfYear
end go
-- Execute function on first day of first week of year from 1990 to 2030 select DT, ISO_WEEK_OF_YEAR = dbo.F_ISO_WEEK_OF_YEAR(a.DT) from ( select DT = getdate()union all select DT = convert(datetime,'1990/01/01') union all select DT = convert(datetime,'1990/12/31') union all select DT = convert(datetime,'1991/12/30') union all select DT = convert(datetime,'1993/01/04') union all select DT = convert(datetime,'1994/01/03') union all select DT = convert(datetime,'1995/01/02') union all select DT = convert(datetime,'1996/01/01') union all select DT = convert(datetime,'1996/12/30') union all select DT = convert(datetime,'1997/12/29') union all select DT = convert(datetime,'1999/01/04') union all select DT = convert(datetime,'2000/01/03') union all select DT = convert(datetime,'2001/01/01') union all select DT = convert(datetime,'2001/12/31') union all select DT = convert(datetime,'2002/12/30') union all select DT = convert(datetime,'2003/12/29') union all select DT = convert(datetime,'2005/01/03') union all select DT = convert(datetime,'2006/01/02') union all select DT = convert(datetime,'2007/01/01') union all select DT = convert(datetime,'2007/12/31') union all select DT = convert(datetime,'2008/12/29') union all select DT = convert(datetime,'2010/01/04') union all select DT = convert(datetime,'2011/01/03') union all select DT = convert(datetime,'2012/01/02') union all select DT = convert(datetime,'2012/12/31') union all select DT = convert(datetime,'2013/12/30') union all select DT = convert(datetime,'2014/12/29') union all select DT = convert(datetime,'2016/01/04') union all select DT = convert(datetime,'2017/01/02') union all select DT = convert(datetime,'2018/01/01') union all select DT = convert(datetime,'2018/12/31') union all select DT = convert(datetime,'2019/12/30') union all select DT = convert(datetime,'2021/01/04') union all select DT = convert(datetime,'2022/01/03') union all select DT = convert(datetime,'2023/01/02') union all select DT = convert(datetime,'2024/01/01') union all select DT = convert(datetime,'2024/12/30') union all select DT = convert(datetime,'2025/12/29') union all select DT = convert(datetime,'2027/01/04') union all select DT = convert(datetime,'2028/01/03') union all select DT = convert(datetime,'2029/01/01') union all select DT = convert(datetime,'2029/12/31') union all select DT = convert(datetime,'2030/12/30') ) a
We are trying to compare our current calendar week (based on Monday being the first day of the week) with the previous calendar week.
I'm trying to produce a line chart with 2 axis:
- x axis; the day of the week (Mon, Tues, Wed etc - it is fine for this to be a # rather than text e.g. 1 = Mon, 2 = Tues etc) - y axis; the cumulative number of orders
The chart needs two series:
Previous Week. The running count of orders placed that week. Current Week. The running count of orders placed this week.
Obviously in such a chart the 'Current Week' series is going not going to have values along the whole axis until the end of the week. This is expected and the aim of the chart is to see the current week compares against the previous week for the same day.
I have two tables:
Orders TableCalendar Table
The calendar table's main date column is [calDate] and there are columns for the usual [calWeekNum], [calMonth] etc.
My measure for counting orders is simply; # Orders: = countrows[orders].
How do I take this measure and then work out my two series. I have tried numerous things such as adapting TOTALMTD(), following articles such as these:
- [URL] ... - [URL] ...
But I have had no luck. The standard cumulative formulas do work e.g. if I wanted a MTD or YTD table I would be ok, it's just adjusting to a WTD that is causing me big issues.
I'd like to add a yesterday dimension member to a new dimension, like a "Time Utility" dimension, that references the second last day of non empty data in a cube.
At the moment, I'm doing this:
Code Snippet
create member [MIA DW].[DATE TIME].[Date].[Yesterday] as [DATE TIME].[Date].&[2007-01-01T00:00:00]
select [Measures].members on 0, non empty [DATE TIME].[Date].members on 1 from [MIA DW] But the [yesterday] member does not seem to belong to [DATE TIME].[Date].members?
So I guess there's two questions:
1) Can I have a new empty dimension which contains all these special members like "Yesterday" or "This Week" and "Last Week" (these last two obviously refer to a set of Dates)
2)How come the Yesterday member is not returned by the .members function?
while(select MAX(wrh) from @tem1 where wrh = 0) < 1 begin update @tem1 set wrh = (select toaccount from @tem1 where reportdate = (select min(reportdate) from @tem1 where wrh = 0))+(select max(wrh) from @tem1) where wrh = (select max(wrh) from @tem1 where wrh = 0 ) and reportdate = (select min(reportdate) from @tem1 where wrh = 0) end
this is the result while executing loop statement .
employeeidreportdatereportatleftatdehdrhwehwrh 129029 Jan 201409:3019:15008:0009:20024:00065:54 129028 Jan 201409:0018:45008:0009:18016:00056:34 129027 Jan 201409:0018:45008:0009:18008:0009:18 129025 Jan 201408:0010:00005:0002:00045:00047:16 -- week end 129024 Jan 201409:1718:45008:0009:01040:00045:16 129023 Jan 201409:1918:46008:0009:06032:00036:15 129022 Jan 201409:1718:47008:0009:05024:00027:09 129021 Jan 201409:1618:35008:0008:46016:00018:04 129020 Jan 201409:1818:55008:0009:03008:0009:03
How to update only that week hrs , don't continue next week...
In my reports I am extracting the data of number of people joined in all the weeks of the year. And in one of reports I have to extract the data of the number of people joined until the last week from the first week. I am trying out all the logics but nothing is working for me as such. Can any one help me with this issue??????
I have a query that run every day to update a summary table which has week number and day of week. what I currently do is delete all records from the summary table and then summarize all the data availabe from four tables adn then populate the table daily. I want to know if I can run the update query to run only for the week number and day of week depending on getdate. Can I do this?
I am trying to convert a field that has a Date and Time in smallDateTime format into something that will give me the day of the week that each Date corresponds to. For example, if my Date/Time field says "Jun 7 2001 09:30:00" I want my Day field to automatically say "Thursday". Is there a function that will do this? Or is there something close that will convert a variable of smalldatetime format into a number 0-6 which then can be converted to a day of the week?
How do I see which is the first day of the week setting in my server? I was reading about @@DATEFIRST and SET FIRSTDATE, etc. But I don't want to set anything, I just want to know what is the first day of the week set on my server.
I need to write a user defined function that will return the date of the first day of a week when provided with the week number. I had an idea of using a while loop with that will keep adding 1 day from January 1st until the week number (found via the DATEPART function) is equal to the supplied week number. Not sure if this is the best way though - any ideas?
I have this code below and it appears to work, but I wondered if there was a better way.
Basically I have a table with some sales. The amount sold is stored in a varchar (for a very stupid reason) called ticketNo, the date of the sale is stored in txDate (thankfully this is a date).
We need to report out the total amount sold for a period in weekly groupings with the week starting on Monday. If a week starts within the period specified but does not finish we extend the period to include the whole week.
As I said the code below appears to work (no I have not tested extensively), but it looks like it will be pretty resouce intensive.
set @minTxDate = '02-Jul-2007' set @maxTxDate = '31-Aug-2007'
declare @StartOfYear DateTime declare @LocalMaxTxDate DateTime declare @WeekDay int declare @StartWeek int declare @EndWeek int
set @StartOfYear = '01-Jan-' + Convert(varchar(4),Year(getDate())) set @WeekDay = DatePart(weekday,@StartOfYear) set @StartWeek = DatePart(week, @minTxDate) set @EndWeek = DatePart(week, @maxTxDate)
set @LocalMaxTxDate = DateAdd(d,10,@maxTxDate)
set DATEFIRST 1 select DateAdd(d,((DatePart(week, tmpSale.txDate)-1)*7)+@WeekDay-1, @StartOfYear) 'FirstDayOfWeek', DatePart(week, tmpSale.txDate) 'WeekOfSale', COALESCE(sum (Convert(decimal, ticketNo)),0) 'TonnesSold' from (select Sale.txDate, Sale.ticketNo from Sale where Sale.txDate between @minTxDate and @LocalMaxTxDate) tmpSale where DatePart(week, tmpSale.txDate) between @StartWeek and @EndWeek group by DatePart(week, tmpSale.txDate), DateAdd(d,((DatePart(week, tmpSale.txDate)-1)*7)+@WeekDay-1, @StartOfYear)
I am trying to calculate the number of the week within a month of a given date. in other words, given a date I need to find out whether it's the 1st week or 2nd or 3rd or 4th or 5th week of that given month.
for instance let's call my function "weekOfMonth()":
weekOfMonth(4/19/2004) should return 4 (it's the fourth week of april) weekOfMonth(4/7/2004) should return 2 (it's the second week of april) etc ..
