Post reply

Set corect week from datepart

  • June 23, 2011 at 1:01 am

    #148242

    HI

    Using

    select datepart(week,getdate())

    I get week 26, but the correct week is 25.

    I have cheked:

    DATEFIRST = 1

    @@Language = Norwegian

    @@langid = 8

    But when I look at the server properties it says English (United States)

    After some time on google I found that the last parameter cannot be changed.

    Any one got an idea on how to get correct week?

    Dan

  • June 23, 2011 at 2:25 am

    #1342859

    Just out of interest how do you work out that today should be week 25?

    ***The first step is always the hardest *******

  • June 23, 2011 at 2:31 am

    #1342862

    Hi

    In Norway this is week 25.

    Dan

  • June 23, 2011 at 3:42 am

    #1342897

    HI, well im not sure if you can change the system variable to get what you want however if norway are 1 week behind you could use dateadd to take 1 week away from getdate to get your week 25

    select datepart(week,dateadd(ww,-1,getdate()))

    ***The first step is always the hardest *******

  • June 23, 2011 at 4:35 am

    #1342905

    Hi

    That would leave me with week 0 when it returns week 1.

    .

    Dan

  • June 23, 2011 at 5:05 am

    #1342918

    HI

    you would get week 52 not 0

    ***The first step is always the hardest *******

  • June 27, 2011 at 7:13 am

    #1344492

    Hi again

    Trying to use a function to solve this.

    USE MASTER

    CREATE FUNCTION Getweek (@DATE datetime)

    RETURNS int

    WITH EXECUTE AS CALLER

    AS

    BEGIN

    DECLARE @weeknumber int;

    SET @weeknumber= DATEPART(wk,@DATE)+1

    -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104');

    --Special cases: Jan 1-3 may belong to the previous year

    IF (@weeknumber=0)

    SET @weeknumber=dbo.weeknumber(CAST(DATEPART(yy,@DATE)-1

    AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1;

    --Special case: Dec 29-31 tilhører neste år

    IF ((DATEPART(mm,@DATE)=12) AND

    ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))

    SET @weeknumber=1;

    RETURN(@weeknumber);

    END;

    GO

    I then try to use it in a query:

    SELECT top 10

    [Date character] as dates,

    Getweek(CONVERT(DATETIME,[Date character] ,101)) AS 'Week'

    from catalog.dbo.[test system calender]

    The [test system calender]

    only contains dates and [date character] i.e:

    20110625, 2011-06-25

    20110626, 2011-06-26

    20110627, 2011-06-27

    and so on

    But I cant get it to run.

    Error:

    Error: 'Getweek' is not a recognized built-in function name. (State:37000, Native Code: C3)

    Dan

  • June 28, 2011 at 12:10 pm

    #1345407

    I would set up a calendar table for this type of thing. That way you always get exactly what you want. Here are some links showing how to set them up and use them. The last one is a weekly calendar table and may be useful to you:

    http://www.sqlservercentral.com/articles/T-SQL/70482/

    http://www.sqlservercentral.com/articles/T-SQL/70743/

    http://www.sqlservercentral.com/articles/T-SQL/72345/

    Hope this helps.

    Todd Fifield

  • June 29, 2011 at 10:29 am

    #1346044

    I agree with glen.wass that the easiest way is to do what he suggested and you may need to include a check to see if it is week 52 of the previous year which I believe is the case for Jan. 1, 2011. This would be week 52 of 2010.

    If you don't stand for something, you'll fall for anything!,

    Don Urquhart

  • June 29, 2011 at 1:24 pm

    #1346190

    Yes.

    In US first week starts on first of january, and usually is less than 7 days. Here in Norway, those days are included in week 52, and the first week is the first complete week.

    This is the kind of stuff that I would expect to be dealt by country and language settings.

    best,

    Eero

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply