Need some help with a select statement

Question

Post reply

Need some help with a select statement

Ud4inBrampton

SSC-Addicted

Points: 408
More actions
May 17, 2006 at 8:01 pm

#114654

Hello everyone,
I need to create a recordset of records, which are 3 days old from a table named Members. The column which contains the timestamp is named JoinDate.
The timestamp was generated using (getdate). I've tried a couple of different statements with absolutely no luck, any help will be greatly appreciated!
Thanks, Robert

Phone a Babysitter.com
Connecting Parents and Babysitters Nationwide!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

you want to use the datediff function for this:

SELECT DATEDIFF(day, somedatecolumn, getdate()) AS no_of_days from sometable

SELECT * from sometable where DATEDIFF(day, somedatecolumn, getdate()) =3 --exactly 3 days old

SELECT * from sometable where DATEDIFF(day, somedatecolumn, getdate()) >=3 --3 days or older

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

For performance reasons it is better to use a statement like this one :

CREATE PROC dbo.DemoDateRange @iDaysOld as int

AS

SET NOCOUNT ON

Declare @DateStart as datetime

--Today's date without the time

SET @DateStart = DATEADD(D,0,DATEDIFF(D, 0,GetDate()))

--Rollback the number of days

SET @DateStart = DATEADD(D, -1 * @iDAysOld, @DateStart)

--all in one go :

SET @DateStart = DATEADD(D, -1 * @iDAysOld, DATEADD(D,0,DATEDIFF(D, 0,GetDate())))

PRINT @DateStart

Select * from dbo.SysObjects where crDate > @DateStart

SET NOCOUNT OFF

GO

exec dbo.DemoDateRange 1

GO

DROP PROCEDURE dbo.DemoDateRange

That way if an index can be used you will have an index seek instead of an index scan.

Marshall Smith SSCrazy Points: 2041 More actions · Answer 3

If you want better performance (to avoid the table scan), reverse the comparison and use DATEADD:

SELECT *

FROM sometable

WHERE JoinDate BETWEEN DATEADD(d,-3,GETDATE()) AND DATEADD(d,-2,GETDATE())

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

That works too... but that doesn't take the consideration of the time in the date which is very often required in a search.

Also as a best pratice I try to avoid putting functions in the where clause, but it's not a problem in this case.

Ud4inBrampton SSC-Addicted Points: 408 More actions · Answer 5

Much thanks to everyone that replied!

I used Lowells statement as it did return all entries regardless of the time entered, and since I only need to run it once per day, (although appreciated!) the proc wasn't necessary.

Thanks again,

Robert

Phone a Babysitter.com
Connecting Parents and Babysitters Nationwide!