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: 183686 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

Voide

Mr or Mrs. 500

Points: 548

July 15, 2013 at 3:50 pm

#1632689

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: 100262 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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

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

riya_dave

Hall of Fame

Points: 3106

July 16, 2013 at 7:53 am

#1632876

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.