SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get data for the past one year (365 days)


Get data for the past one year (365 days)

Author
Message
sharonsql2013
sharonsql2013
Mr or Mrs. 500
Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)

Group: General Forum Members
Points: 594 Visits: 1265
I am trying to view data for past one year ...Don't know whats wrong with the following:

select ProdMonth ,ProdYear from Production
where DATEADD(yyyy,-1,getdate())


Kindly advise
batgirl
batgirl
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1707 Visits: 1820
sharonsql2013 (8/23/2013)
I am trying to view data for past one year ...Don't know whats wrong with the following:

select ProdMonth ,ProdYear from Production
where DATEADD(yyyy,-1,getdate())


Kindly advise



You will need to compare some data column to the calculated date one year ago.

....where DATECOLUMN >= DATEADD(yyyy,-1,getdate())
sharonsql2013
sharonsql2013
Mr or Mrs. 500
Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)

Group: General Forum Members
Points: 594 Visits: 1265
select ProdMonth ,ProdYear from Production
where ProdYear >= DATEADD(yyyy,-1,getdate())


returns no data... However , there is data present. DOn't know whats wrong...
David Webb-CDS
David Webb-CDS
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1558 Visits: 8586
What's the data type of prodyear? If you are comparing an entire date against a column that only contains the year as an int or a 4 position varchar, you won't get what you are looking for.



And then again, I might be wrong ...
David Webb
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85923 Visits: 41091
batgirl (8/23/2013)
sharonsql2013 (8/23/2013)
I am trying to view data for past one year ...Don't know whats wrong with the following:

select ProdMonth ,ProdYear from Production
where DATEADD(yyyy,-1,getdate())


Kindly advise



You will need to compare some data column to the calculated date one year ago.

....where DATECOLUMN >= DATEADD(yyyy,-1,getdate())


This is one of the biggest reasons why I try to discourage people from saving parts of dates in separate columns.

What is the datatype of the ProdYear and ProdMonth columns?

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sharonsql2013
sharonsql2013
Mr or Mrs. 500
Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)

Group: General Forum Members
Points: 594 Visits: 1265
Yes , Prod year is int.
David Webb-CDS
David Webb-CDS
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1558 Visits: 8586
So it would have to be something like:

select ProdMonth ,ProdYear from Production
where ProdYear >= cast(datepart(yyyy,DATEADD(yyyy,-1,getdate())) as int)




I second Jeff's suggestion to store dates as dates.



And then again, I might be wrong ...
David Webb
sharonsql2013
sharonsql2013
Mr or Mrs. 500
Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)

Group: General Forum Members
Points: 594 Visits: 1265
That sure helps.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85923 Visits: 41091
sharonsql2013 (8/23/2013)
Yes , Prod year is int.


What about ProdMonth? We'll need that info to identify rows that are a year old. And, because you only have Year and Month data, it's going to be impossible to figure out what rows are available in terms of 365 days.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Ed Wagner
Ed Wagner
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16609 Visits: 10063
David Webb-CDS (8/23/2013)

I second Jeff's suggestion to store dates as dates.

I have to third Jeff's "store dates as dates" recommendation. I seem to spend a lot of time trying to convince people of this. Some people like to store some date parts differently or make up their own approach. Once you get bitten by doing this, you'll convert and stay converted. Your data will then follow. :-)

One word of caution, however, is that the datetime data type is a point in time, not a duration of time. I've seen where people get in trouble trying to store a duration as a datetime data type.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search