SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query Tables Where Date between now and 7 days ago
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Wabby
Starting Member

27 Posts

Posted - 06/18/2008 :  08:51:14  Show Profile  Reply with Quote
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

India
22761 Posts

Posted - 06/18/2008 :  08:53:20  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Wabby
Starting Member

27 Posts

Posted - 06/18/2008 :  09:00:08  Show Profile  Reply with Quote
Legend!

I now understand how that works aswell :D

Thanks VERY much.

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

Cheers
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 06/18/2008 :  09:09:07  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Wabby
Starting Member

27 Posts

Posted - 06/18/2008 :  09:09:25  Show Profile  Reply with Quote
Also, any idea on how to convert from datetime to a string?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 06/18/2008 :  09:12:04  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Wabby
Starting Member

27 Posts

Posted - 06/18/2008 :  09:24:50  Show Profile  Reply with Quote
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 :)
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 06/18/2008 :  09:29:23  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Bluw
Starting Member

USA
1 Posts

Posted - 09/19/2012 :  14:45:03  Show Profile  Reply with Quote
if in criteria field in MSAccess in a query, type: >Now()-7

KWH-+=something
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 10/05/2012 :  02:27:27  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by Bluw

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

KWH-+=something


But the question is posted in SQL Server forum

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000