We help IT Professionals succeed at work.

workweek on MS SQL 2000

cwteoh
cwteoh asked
on
Medium Priority
551 Views
Last Modified: 2008-02-01
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?
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
Top Expert 2006
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Top Expert 2006

Commented:
hi
Is this solution working for you or you need further assistance?
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
point split
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.