Post reply

Query table

  • December 12, 2014 at 8:53 am

    #296026

    I am trying to query a table that has data stored week wise in columns like this ....

    SELECT [Name]

    ,[Activity]

    ,[3-Nov-14]

    ,[10-Nov-14]

    ,[17-Nov-14]

    ,[24-Nov-14]

    ,[1-Dec-14]

    ,[8-Dec-14]

    ,[15-Dec-14]

    ,[22-Dec-14]

    ,[29-Dec-14]

    ,[YTD_2014]

    From dbo.test

    However , I want to query the data in the current week that is week of 8-dec-2014 without hard-coding the column (8-dec-2014)

    Please suggest.

  • December 12, 2014 at 9:07 am

    #1764470

    Try using this

    declare @ThisDate datetime;

    set @ThisDate = getdate();

    select dateadd(wk, datediff(wk, 0, @ThisDate), 0)

    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • December 12, 2014 at 9:08 am

    #1764471

    Thank you!

  • December 12, 2014 at 9:10 am

    #1764473

    You're welcome.

    http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/ has a lot of useful date things if you need more.

    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • December 12, 2014 at 12:45 pm

    #1764563

    BWFC (12/12/2014)

    Try using this

    declare @ThisDate datetime;

    set @ThisDate = getdate();

    select dateadd(wk, datediff(wk, 0, @ThisDate), 0)

    Be advised that may not work as expected on all installations because the WK datepart boundaries are based on the value of DATEFIRST, which can vary even with something as simple as a language change (for example).

    If you want to force dates that "start on Mondays", you might want to use the following...

    SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7*7,0)

    It looks weird to first divide by 7 and then turn right around and multiply by 7 but it's integer math. The divide will drop the partial week (partial 7 day period).

    The "0" date is the First of January, 1900, which was a Monday.

    --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)

  • December 12, 2014 at 2:18 pm

    #1764574

    Great! Thank you

Viewing 6 posts - 1 through 5 (of 5 total)

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