Solved

returning all records entered in a week

Posted on 1997-11-17
2
216 Views
Last Modified: 2012-08-13
I have two problems with SQL

problem 1

I'm using LiveWire Pro to connect to a DB in MS-SQL.  I need to return all records entered in the last week or the last 10 days or 2 weeks etc. etc.

problem 2

I can entered carriage returns in my textbox which are stored in the DB.  Whenever I retrieve the data from the DB, CR's are stripped out.  I'm not putting the data from the DB back into a textbox - just into a formatted table
0
Comment
Question by:bode
2 Comments
 
LVL 10

Accepted Solution

by:
bret earned 100 total points
ID: 1089131
1)  

declare @days_back
select @days_back = -7
Select * from table
where enter_date > dateadd(day,-7,getdate()).

The sql above will get you all records
entered in the last 7 days.  If you run this at 2:22 pm
on Monday, Nov 17th, you will get everything entered since
2:22 pm Monday, Nov 10th.  If you want everything from the
midnight preceeding Nov 10th, you need to strip the time information from the datetime [note that any date will
work as the initial @referencedate, it is completely arbitrary]:

declare @referencedate datetime
select @referencedate = "1/1/1950"
select @referencedate = dateadd(day,datediff(day,@referencedate,getdate()),@referencedate)
select * from mytable
where insert_date > dateadd('day',-7,@referencedate)

2)  Don't know.  (These are two completely unrelated questions.
You really should open them as seperate questions (at half the points each, perhaps))
0
 

Author Comment

by:bode
ID: 1089132
Thanks Bret

I've actually already solved the problem!  I used the following:

SELECT * FROM mytable WHERE DATEDIFF(DD, mydatefield, GETDATE()) BETWEEN 0 AND 7

This selects all records entered in the last 7 days
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now