Last 3 months data

  • I need to see records for last three months ie Dec , Nov, Oct.

    No matter what the date is today...

    Any advise?

    My columnname is Time_Open which contains all the dates...

  • Something like this?

    select *

    from YourTable

    where Time_Open > dateadd(month, datediff(month, 0, GETDATE()) - 4, 0)

    and Time_Open < dateadd(month, datediff(month, 0, GETDATE()) - 1, 0)

    --edit--

    Fixed a couple typos.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Thanks.

  • sharonsql2013 (1/9/2014)


    Thanks.

    Do you understand how the formulas work?

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

  • You'd want to use getdate() to find the current day and date and use that in a formula with dateadd.

  • trickg1 (1/20/2014)


    You'd want to use getdate() to find the current day and date and use that in a formula with dateadd.

    Welcome aboard. Do you have any code to support your suggestion or is Sean's good enough?

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

  • Off the top of my head, no - I wanted to post yesterday in the noobs' section to get a post on the board. I've used things I've learned on this forum a lot over the years, and only yesterday I realized after trying to log in on a whim (with credentials I use on other forums) that I have had an account here since 2008.

    I'll be better about posting code bits here and there from this point forward.

  • Such a simple question, but it made me think... There has to be a way to do this via date_sub, or datediff isn't there? I'll work on it, fun/valuable puzzle for me...

  • Would something like this work? I wrote it in "notepad" so not sure if syntax is correct...

    SELECT *

    FROM Table

    WHERE Time_Open <= GETDATE()

    AND Time_Open >= DATE_SUB(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 3, 0)

  • rho_pooka (1/22/2014)


    Would something like this work? I wrote it in "notepad" so not sure if syntax is correct...

    SELECT *

    FROM Table

    WHERE Time_Open <= GETDATE()

    AND Time_Open >= DATE_SUB(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 3, 0)

    It might be correct for MySQL but DATE_SUB is not a valid function in sql server. I suspect it will work just like sql server. This will get you EXACTLY 3 months ago, including the timestamp. That means that when you posted this on January 22 it would not return any data from October before the 22nd. This is why you have to do a couple of additional steps like I did so you start at the beginning of the month from 3 months ago.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (1/23/2014)


    rho_pooka (1/22/2014)


    Would something like this work? I wrote it in "notepad" so not sure if syntax is correct...

    SELECT *

    FROM Table

    WHERE Time_Open <= GETDATE()

    AND Time_Open >= DATE_SUB(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 3, 0)

    It might be correct for MySQL but DATE_SUB is not a valid function in sql server. I suspect it will work just like sql server. This will get you EXACTLY 3 months ago, including the timestamp. That means that when you posted this on January 22 it would not return any data from October before the 22nd. This is why you have to do a couple of additional steps like I did so you start at the beginning of the month from 3 months ago.

    Interesting thanks Sean!

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

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