[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 537
  • Last Modified:

workweek on MS SQL 2000

example:

datepart(ww,convert(datetime,'01/JAN/2005',113)) = 01
datepart(ww,convert(datetime,'31/DEC/2004',113)) = 53

with these conversions... I got my sale forecast data incorrect! can anyone help me solve this problem? It happened every year.

I want it like this

this is date format "dd/MMM/YYYY"

when 31/DEC/2004 is week 53 and 01/JAN/2005 should be week 53 as well. Because 07/JAN/2005 suppose to be the first week of the year. Unless 01/JAN/2005 fall on Monday.

show I write a store procedure to get the correct answer?
0
cwteoh
Asked:
cwteoh
  • 2
  • 2
1 Solution
 
imran_fastCommented:
CREATE PROCEDURE dbo.GETWEEK_STARTDATE  @DATE DATETIME,@WEEKNUM AS TINYINT OUTPUT
AS
BEGIN
SET DATEFIRST 7
DECLARE
     @START_DATE DATETIME,
     @FIRSTWEEKDAY AS TINYINT
     SET @FIRSTWEEKDAY = 1
SELECT @START_DATE = '1/1/'+ CAST(DATEPART(year,getdate()) AS VARCHAR(4))


WHILE DATEPART(weekday, @START_DATE) <> 1
BEGIN
     SET @FIRSTWEEKDAY =@FIRSTWEEKDAY + 1
     SET @START_DATE = dateadd(day, 1, @START_DATE)
     
END

     WHILE @DATE NOT BETWEEN dateadd(day,@WEEKNUM-1,@START_DATE) AND dateadd(day,6*@WEEKNUM,@START_DATE)
     SET @WEEKNUM= @WEEKNUM +1
     
END
GO
--
-- CALLING THE PROCEDURE
-- ================
-- DECLARE @WEEKNUM  INT
-- EXEC GETWEEK_STARTDATE '01/JAN/2005', @WEEKNUM OUTPUT
-- SELECT @WEEKNUM
0
 
imran_fastCommented:
hi
Is this solution working for you or you need further assistance?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
point split
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now