Solved

returning all records entered in a week

Posted on 1997-11-17
2
247 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

739 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