CALCULATE 2 years back data

Question

Post reply

CALCULATE 2 years back data

riya_dave

Hall of Fame

Points: 3106
More actions
July 15, 2013 at 2:58 pm

#280244

how to calculate 2 years back data
suppose today 07/15/2013 ,2 years back ,07/15/2011.
how to calculate exact date?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Using DATEADD

SELECT DATEADD(YEAR, -2, GETDATE()), CAST( DATEADD(YEAR, -2, GETDATE()) AS DATE)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Voide Mr or Mrs. 500 Points: 548 More actions · Answer 2

Assuming you're talking about the create timestamp of the data, then this could work setting tablename to whatever your table is called.

DATEADD feature is used with CURRENT_TIMESTAMP. Useful to know, look it up.

select * from tablename

where create_timestamp > DATEADD(yy,-2,current_timestamp)

ScottPletcher SSC Guru Points: 101101 More actions · Answer 3

In general, as below. But, if the column is not datetime, CAST the date calc to the exact datetime of the column. For example, if the column is date, do:

CAST(DATEADD(YEAR, -2, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AS date)

SELECT ...

FROM dbo.tablename

WHERE

datetime_column >= DATEADD(YEAR, -2, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

riya_dave Hall of Fame Points: 3106 More actions · Answer 4

i dotn understand why it is > , it should be less than

i want to keep only 2 years data in my current database , rest should move to another table

so it should be

rundate< dateadd(yy,-2,getdate())

Voide Mr or Mrs. 500 Points: 548 More actions · Answer 5

Assuming you want all the data collected from today until two years ago to this date then the logic is:

create_timestamp > (The function you're using to go back to exactly two years ago today)

Example:

2012-7-16 > 2011-7-16.

You're looking for all entries NEWER than two years ago to this date. Unless I've mistaken your request, you're not exactly a very detail orientated poster.

riya_dave Hall of Fame Points: 3106 More actions · Answer 6

ok

let me try to explain here

i want to move data that is older than 2 years.

for example.

dateorder < dateadd(yy,-2,getdate()

here all the data which is less than 2 years will be move to another table

ScottPletcher SSC Guru Points: 101101 More actions · Answer 7

OK, so:

datetime_column < DATEADD(YEAR, -2, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".