Last 3 months data

Question

Post reply

Last 3 months data

sharonsql2013

SSCertifiable

Points: 5908
More actions
January 9, 2014 at 2:43 pm

#281081

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

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

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/

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 2

sharonsql2013

SSCertifiable

Points: 5908

January 9, 2014 at 3:05 pm

#1678997

Thanks.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

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)

trickg1 SSC Journeyman Points: 82 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

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)

trickg1 SSC Journeyman Points: 82 More actions · Answer 6

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.

rho_pooka Ten Centuries Points: 1385 More actions · Answer 7

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

rho_pooka Ten Centuries Points: 1385 More actions · Answer 8

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)

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

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/

rho_pooka Ten Centuries Points: 1385 More actions · Answer 10

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!