Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get data for the past one year (365 days) Expand / Collapse
Author
Message
Posted Friday, August 23, 2013 10:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 10:45 AM
Points: 229, Visits: 700
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
Post #1487912
Posted Friday, August 23, 2013 10:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:50 PM
Points: 1,425, Visits: 1,685
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())
Post #1487922
Posted Friday, August 23, 2013 11:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 10:45 AM
Points: 229, Visits: 700
select ProdMonth ,ProdYear from Production
where ProdYear >= DATEADD(yyyy,-1,getdate())


returns no data... However , there is data present. DOn't know whats wrong...
Post #1487946
Posted Friday, August 23, 2013 11:21 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 863, Visits: 7,325
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
Post #1487949
Posted Friday, August 23, 2013 11:26 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:47 AM
Points: 35,347, Visits: 31,882
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1487951
Posted Friday, August 23, 2013 11:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 10:45 AM
Points: 229, Visits: 700
Yes , Prod year is int.
Post #1487954
Posted Friday, August 23, 2013 12:19 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 863, Visits: 7,325
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
Post #1487970
Posted Friday, August 23, 2013 12:26 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 10:45 AM
Points: 229, Visits: 700
That sure helps.
Post #1487978
Posted Friday, August 23, 2013 3:49 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:47 AM
Points: 35,347, Visits: 31,882
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1488046
Posted Wednesday, August 28, 2013 11:37 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:21 PM
Points: 4,202, Visits: 3,639
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
Post #1489357
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse