SQL Year and Week numbers

  • Hi does anyone know how to create a query in SQL 2000 or 2005 to get the year and week number together for a date i.e. the 01/01/2008 would be 200801. I know how to get them seperately using datepart but i need them joined together.

    Any help would be much appreciated.

  • asbains8 (5/14/2008)


    Hi does anyone know how to create a query in SQL 2000 or 2005 to get the year and week number together for a date i.e. the 01/01/2008 would be 200801. I know how to get them seperately using datepart but i need them joined together.

    Any help would be much appreciated.

    Is this what you want??

    select convert(char(4),datename(yyyy,'01/01/2008'))+convert(char(2),datepart (wk,'01/01/2008'))

    --=OR=--

    select convert(char(4),'01/01/2008',112)+convert(char(2),datepart(wk,'01/01/2008'))

    "-=Still Learning=-"

    Lester Policarpio

  • When using DATENAME, it's already in a character format, so you don't need to use CONVERT or CAST...

    SELECT DATENAME(yy,GETDATE())+RIGHT('00'+DATENAME(wk,GETDATE()),2)

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Ack... double post.... :sick:

    http://www.sqlservercentral.com/Forums/Topic500664-338-1.aspx

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (5/14/2008)


    When using DATENAME, it's already in a character format, so you don't need to use CONVERT or CAST...

    SELECT DATENAME(yy,GETDATE())+RIGHT('00'+DATENAME(wk,GETDATE()),2)

    Never thought of this 😛 hehehe thanks... thats the reason i used a convert hehehe .

    This seems to work too..

    SELECT DATENAME(yy,GETDATE())+RIGHT(DATENAME(wk,GETDATE()),2)

    Why does some members tend to double post? :hehe:

    "-=Still Learning=-"

    Lester Policarpio

  • Here are two more options, one returning an integer value.

    declare @date datetime

    select @date = '01/01/2008'

    select year(@date) * 100 + datepart(week,@date)

    select convert(varchar,year(@date) * 100 + datepart(week,@date))

  • Lester Policarpio (5/15/2008)


    Why does some members tend to double post? :hehe:

    I can't actually blame them... they look at all the different forums and think they stand a better chance of someone seeing their post if they hit more than one forum. They don't realize that most of us forum "trolls" view all the daily posts for all the forums.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I'm trying to write an update command in sql to take the weekyear portion of a serial # and convert it back to a date by extracting the week and year "SER074400001" (0744). I can't seem to get the week to calculate back. As you can see below the closest I have come gives me (44/2007).

    Any assistance would be appreciated.

    update field_fact

    set shipdate = substring(serln, 6, 2) + '/20' + SUBSTRING(serln, 4, 2)

    --SELECT substring(serln, 6, 2) +'/20' + SUBSTRING(serln, 4, 2) AS date

    FROM field_fact

    WHERE (NOT (serln LIKE 'n%') AND NOT (serln LIKE '1%'))

    Jenny

  • jennyor (8/11/2008)


    I'm trying to write an update command in sql to take the weekyear portion of a serial # and convert it back to a date by extracting the week and year "SER074400001" (0744). I can't seem to get the week to calculate back. As you can see below the closest I have come gives me (44/2007).

    Any assistance would be appreciated.

    update field_fact

    set shipdate = substring(serln, 6, 2) + '/20' + SUBSTRING(serln, 4, 2)

    --SELECT substring(serln, 6, 2) +'/20' + SUBSTRING(serln, 4, 2) AS date

    FROM field_fact

    WHERE (NOT (serln LIKE 'n%') AND NOT (serln LIKE '1%'))

    Jenny

    Hi, Jenny, and welcome aboard!

    First, just as an FYI... you'd probably do better if you posted you problem separately... it'll get more attention that way. Also, doesn't apply this time, but take a look at the link in my signature line below for when it might.

    As to your problem, I think this might do what you want provided that your week starts on Monday...

    DECLARE @Serial CHAR(12)

    SET @Serial = 'SER074400001'

    SELECT DATEADD(wk,DATEDIFF(wk,0,DATEADD(yy,CAST(SUBSTRING(@Serial,4,2) AS INT),'2000'))+CAST(SUBSTRING(@Serial,6,2)AS INT),0)

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • This may be posted already, but...

    DECLARE @Date DATETIME

    SET @Date = GETDATE()

    SELECT CONVERT(VARCHAR,DATEPART(YEAR,@Date)) + CONVERT(VARCHAR,DATEPART(WEEK,@Date))

  • Jeff, It worked like a charm thanks!

    Jenny

  • Thanks for the feedback, Jenny.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • SELECT CONVERT(VARCHAR,GetDate(),112) 😛

    [font="Comic Sans MS"]Sara[/font]

    A ship in the harbour is safe . . . but that's not what ships were made for. 😀

  • select convert(char(4),datename(yyyy,'01/01/2008'))+convert(char(2),datepart (wk,'01/01/2008'))

  • Sara_DBA (8/13/2008)


    SELECT CONVERT(VARCHAR,GetDate(),112) 😛

    Heh...ok, Sara... if you read Jenny's original request, tell me how your code converts the following, posted in that original request, to a date. 😛

    DECLARE @Serial CHAR(12)

    SET @Serial = 'SER074400001'

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 19 total)

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