Get data for the past one year (365 days)

Question

Get data for the past one year (365 days)

sharonsql2013

SSCertifiable

Points: 5908
More actions
August 23, 2013 at 10:09 am

#298076

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

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

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

batgirl SSCarpal Tunnel Points: 4979 More actions · Answer 1

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 SSCertifiable Points: 5908 More actions · Answer 2

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 SSCoach Points: 17398 More actions · Answer 3

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 SSC Guru Points: 1004356 More actions · Answer 4

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.

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)

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 5

sharonsql2013

SSCertifiable

Points: 5908

August 23, 2013 at 11:29 am

#1644511

Yes , Prod year is int.

David Webb-CDS SSCoach Points: 17398 More actions · Answer 6

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 SSCertifiable Points: 5908 More actions · Answer 7

sharonsql2013

SSCertifiable

Points: 5908

August 23, 2013 at 12:26 pm

#1644533

That sure helps.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

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.

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)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 9

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