CODEWITHSUNDEEP
sqlsql-serversql-server-2005sql-server-2008
juniorbansal
juniorbansal

Reputation: 1271

SQL Server Query to get a number of rows that changed in the past 1 day

I am completely new to SQL Server and don't have any idea about it. I tried writing a query similar to oracle and I dont see expected results.

My table has a column whenChanged - sample value is 2011-10-05 21:22:27.0

There are 100k rows in the table and I want the list of rows which got changed in the last 1 day, 1 hour blah.blah.blah.

I wrote a query like select top 10 * from tablename where whenchanged > whenchanged -1.

But dont see what i want

Upvotes: 1

Views: 4569

Answers (3)

arunkumar.halder
arunkumar.halder

Reputation: 55

In the last hour:

select * from tablename 
where order_date > DateAdd(hour, -1, GETDATE()) and order_date<=GETDATE()

Upvotes: 0

Paul Hunt
Paul Hunt

Reputation: 3555

Try this to get all the records that have been changed in the last day:

select top 10 * from tablename where whenchanged > DATEADD (D, -1, GETDATE())

http://msdn.microsoft.com/en-us/library/ms186819.aspx for the details on the DATEADD function and the other date parts you can specify

EDIT: If you want to get records between 2 dates the something like this will work:

select * from tablename where whenchanged BETWEEN 'Date1ValueGoesHere' AND 'Date2ValueGoesHere'

Upvotes: 4

Adriano Carneiro
Adriano Carneiro

Reputation: 58615

To get the records changed in the last 24 hours:

select * from tablename 
where whenchanged > DateAdd(hour, -24, GETDATE())

In the last hour:

select * from tablename 
where whenchanged > DateAdd(hour, -1, GETDATE())

To get all changed today, you have to tweak a little:

select * from tablename 
where whenchanged >= cast(GETDATE() as date)

The cast eliminates the time part so you'll have 2011-10-05 21:22:27.0 compared to 2011-10-05 which is the same as 2011-10-05 00:00:00.0 in DateTime.

Upvotes: 2

Related Questions