GetDate() over last 7 day - SQL Server Forums

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

Old Forums

CLOSED - General SQL Server

GetDate() over last 7 day

Author

Topic

westmich
Starting Member

35 Posts

Posted - 2004-09-06 : 14:17:05

I am creating a view, but I only want records where the DateCreated field is within the last 7 days. I'd normally do this server-side in ASP but I'd like to keep it in a view if possible.

Westmich
Smart Web Solutions for Smart Clients
http://www.mindscapecreative.com

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-09-06 : 14:38:02

WHERE DateCreated >= CONVERT(VARCHAR,DATEADD(DD,-7,GETDATE()),101)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-06 : 15:34:19

WHERE DateCreated >= CONVERT(DATEADD(DD,-7,convert(varchar(8),GETDATE(),112))

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-06 : 17:56:19

WHERE DateCreated >= GETDATE() - 7

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-06 : 19:09:45

ok
derrickleggett - that only works on mm/dd/yyyy systems

rockmoose - that will give things created later than the current time time 7 days ago and only if datecreated is a datetime. Might be what westmich wants but I doubt it.
But I suspect you knew that.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-09-06 : 21:55:35

He's in the US. The chance of him not using mm/dd/yyyy is slim.

It is possible though.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-07 : 04:00:57

Yes, " WHERE DateCreated >= GETDATE() - 7" is only for datetime datatype,
just added that for completeness in case DateCreated really is a datetime.
And it treats 7 days ago literally, i.e where DateCreated occurred within 168 hours before now.

westmich - For within 7 days back and datetime datatype you could use:
WHERE DateCreated >= DATEADD(DAY,-7,FLOOR(CAST(GETDATE() AS FLOAT)))

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

westmich
Starting Member

35 Posts

Posted - 2004-09-07 : 10:43:07

Thanks for the help. I am not too concerned on the time, so I think rockmosse's solution will probaly work best.

Westmich
Smart Web Solutions for Smart Clients
http://www.mindscapecreative.com

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-07 : 11:00:06

or:

WHERE DateCreated >= dateadd(dd,-7,datediff(dd,0,getdate()))

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources