February 2, 2009 at 6:53 am
can you tell me t-sql for selecting last 3 months rows from table having datefield -ArriveDate in comaprison with today's date getdate()?
February 2, 2009 at 6:57 am
Use Google or Books Online (BOL) to search for the DateADD function.
February 2, 2009 at 7:26 am
...This is what you are looking for
DECLARE @sdtCurrentDate SMALLDATETIME,
@sdtLastDate SMALLDATETIME,
@tiMonthsToAdd TINYINT
SELECT@sdtCurrentDate = DATEADD( DAY, 1, DATEDIFF( DAY, 0, GETDATE() ) ),
@tiMonthsToAdd = 3,
@sdtLastDate = DATEADD( MONTH, @tiMonthsToAdd, @sdtCurrentDate )
SELECT*
FROMSomeTable
WHEREArriveDate >= @sdtLastDate
AND ArriveDate < @sdtCurrentDate
--Ramesh
February 3, 2009 at 3:24 pm
I believe you would want to use -3 to get the prior 3 months boundary.
February 3, 2009 at 3:33 pm
I'd need a little more information regarding what you are looking for regarding the past 3 months. There are several ways to "read" that requirement. The last three months from todays date (Today is February 3, 2009, so go back to November 2, 2008), or current month plus the previous 2 months (or maybe 3 months).
February 4, 2009 at 7:26 am
That is an excellent point Lynn. I have battled with that such difference several times myself in the past.
February 4, 2009 at 8:20 am
So, rpatil22, answer the question are you? Help you I would like.
February 4, 2009 at 9:17 am
Table will contain data for a year (2008 and 2009) (400K+ rows)
Consider Today's date - 02/04/2009
So 3 months back date is - 10/04/2008.
so i want to delete rows from 10/04/2008 and previous....
also tell fastest t-sql?
February 4, 2009 at 9:26 am
rpatil22 (2/4/2009)
Table will contain data for a year (2008 and 2009) (400K+ rows)Consider Today's date - 02/04/2009
So 3 months back date is - 10/04/2008.
so i want to delete rows from 10/04/2008 and previous....
also tell fastest t-sql?
at its simplest, the following is what you need. If you want more detail, please read the first article linked below in my signature block regarding asking for assistance. the more information you provide, the better answers you will receive in return.
/*
Table will contain data for a year (2008 and 2009) (400K+ rows)
Consider Today's date - 02/04/2009
So 3 months back date is - 10/04/2008. -- Actually, this is 4 months
so i want to delete rows from 10/04/2008 and previous....
also tell fastest t-sql?
*/
delete from dbo.table
where
SomeDateColumn <= (select dateadd(mm,-4,dateadd(dd,datediff(dd,0,getdate()),0)));
February 4, 2009 at 2:27 pm
and which is fastest method for transfering same data to other table instead of deleting it?
inserting is taking lot of time.
February 4, 2009 at 2:44 pm
There is no transfer command. If you are archiving the data in other table, you have to insert it there, then delete it from the source table.
February 5, 2009 at 2:04 am
John Dempsey (2/3/2009)
I believe you would want to use -3 to get the prior 3 months boundary.
Just an oversight from myside, thanks john for correcting it.
--Ramesh
February 8, 2009 at 3:50 am
hi
select *
from TableName
where DateField Between DATEDIFF(day, GetDate(), 90) and getdate()
February 8, 2009 at 5:03 pm
rpatil22 (2/4/2009)
and which is fastest method for transfering same data to other table instead of deleting it?inserting is taking lot of time.
1. Create FOR DELETE trigger on your table to insert deleted rows into other table:
INSERT INTO dbo.OtherTable
(Columns list)
SELECT Columns list
FROM dbo.YourTable
2. Delete rows by small portions.
Use Lynn's script but add TOP 100 after DELETE.
Run that query every time you insert something into YourTable.
If there are more than 100 rows to delete only 100 if them will be deleted.
If it still takes too long reduce the number to 50, 20, until you've reached acceptable performance.
Eventually you'll get to the point when there are less rows to delete than you specified in TOP, so it would not affect anything any more.
_____________
Code for TallyGenerator
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply