Query Tables Where Date between now and 7 days ago

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

General SQL Server Forums

New to SQL Server Programming

Query Tables Where Date between now and 7 days ago

Author

Topic

Wabby
Starting Member

27 Posts

Posted - 2008-06-18 : 08:51:14

Hi Guys,

I currently have a large database, and I wish to query it.
It is running MSSQl 2000.

I wish to have a query that when ran, returns results between NOW() and 7 days ago (A weekly report from the current date).

The date/time field I am sorting by is stored as YY-MM-DD 00:00:00

Any help is appreciated :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-18 : 08:53:20

What is the datatype of datetime column?

If it is datetime datatype

Select columns from your_table where date_col>dateadd(day,-7,getdate())

Madhivanan

Failing to plan is Planning to fail

Wabby
Starting Member

27 Posts

Posted - 2008-06-18 : 09:00:08

Legend!

I now understand how that works aswell :D

Thanks VERY much.

P.S - Any suggestions on good SQL books? Need to learn.

Cheers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-18 : 09:09:07

quote:
Originally posted by Wabby

Legend!

I now understand how that works aswell :D

Thanks VERY much.

P.S - Any suggestions on good SQL books? Need to learn.

Cheers

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

Wabby
Starting Member

27 Posts

Posted - 2008-06-18 : 09:09:25

Also, any idea on how to convert from datetime to a string?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-18 : 09:12:04

quote:
Originally posted by Wabby

Also, any idea on how to convert from datetime to a string?

You need to use CONVERT function
But if you want to format the dates, better do it in the front end application

Madhivanan

Failing to plan is Planning to fail

Wabby
Starting Member

27 Posts

Posted - 2008-06-18 : 09:24:50

Done :)

Select columns from your_table where date_col>dateadd(day,-7,getdate())

becomes

Select columns from your_table where date_col>Convert(varchar(20),dateadd(day,-7,getdate()))

Seems to work for me, so all is good :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-18 : 09:29:23

quote:
Originally posted by Wabby

Done :)

Select columns from your_table where date_col>dateadd(day,-7,getdate())

becomes

Select columns from your_table where date_col>Convert(varchar(20),dateadd(day,-7,getdate()))

Seems to work for me, so all is good :)

Does it mean your datecol is of varchar datatype?

Madhivanan

Failing to plan is Planning to fail

Bluw
Starting Member

1 Post

Posted - 2012-09-19 : 14:45:03

if in criteria field in MSAccess in a query, type: >Now()-7

KWH-+=something

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-10-05 : 02:27:27