Selecting records based on dateadd

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Selecting records based on dateadd

Author

Topic

Skydolphin
Starting Member

43 Posts

Posted - 2008-10-30 : 17:50:02

Hi all,

I need to write a stored proc that allows me to select all records where a submit is greater that the current date minus one year. So for today anything that was submitted after 10/30/2007. What I am thinking is something like this.

declare @DateNow datetime
SET @DateNow = getdate()

SELECT field FROM tbl where SubmitDate > DATEADD(Year, -1, @DateNow)

Am I even close? If not, how do I do this?

Thanks,

Rhonda

Rhonda

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-30 : 17:58:31

Almost:


declare @DateNow datetime
SET @DateNow = getdate()
select field from tbl where submitdate >= dateadd(year, -1, dateadd(day,datediff(day,0,dateadd(day,1,@DateNow)),0))
or

convert(varchar(10),cast(submitdate as datetime),101) > convert(varchar(10),dateadd(year,-1,@DateNow),101)

Skydolphin
Starting Member

43 Posts

Posted - 2008-10-30 : 18:01:00

Thanks. I was sure I was on the right track but not positive of the full syntax.

Rhonda

Rhonda

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-30 : 18:06:42

Basically you want to avoid the TIME part. For example:

Your dateadd(year,-1,getdate()) will return something like '2007-10-30 18:05:26.560'
If your timesubmit is '2007-10-30 23:00:00.000'

Then your query will include this record which is wrong.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 00:30:22

quote:

Originally posted by hanbingl

Almost:


declare @DateNow datetime
SET @DateNow = getdate()
select field from tbl where submitdate >= dateadd(year, -1, dateadd(day,datediff(day,0,dateadd(day,1,@DateNow)),0))
or

convert(varchar(10),cast(submitdate as datetime),101) > convert(varchar(10),dateadd(year,-1,@DateNow),101)

using second method will cause any available indexes on submitdate to be ignored

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-31 : 02:49:49

quote:
Originally posted by visakh16

quote:
Originally posted by hanbingl

Almost:

declare @DateNow datetime
SET @DateNow = getdate()
select field from tbl where submitdate >= dateadd(year, -1, dateadd(day,datediff(day,0,dateadd(day,1,@DateNow)),0))
or

convert(varchar(10),cast(submitdate as datetime),101) > convert(varchar(10),dateadd(year,-1,@DateNow),101)

using second method will cause any available indexes on submitdate to be ignored

Yes. I already suggested that to hanbingl in other thread

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 02:56:04

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

quote:
Originally posted by hanbingl

Almost:

declare @DateNow datetime
SET @DateNow = getdate()
select field from tbl where submitdate >= dateadd(year, -1, dateadd(day,datediff(day,0,dateadd(day,1,@DateNow)),0))
or

convert(varchar(10),cast(submitdate as datetime),101) > convert(varchar(10),dateadd(year,-1,@DateNow),101)

using second method will cause any available indexes on submitdate to be ignored

Yes. I already suggested that to hanbingl in other thread

Madhivanan

Failing to plan is Planning to fail

Sorry didnt notice it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources