• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

returning all records entered in a week

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
bode
Asked:
bode
1 Solution
 
bretCommented:
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
 
bodeAuthor Commented:
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

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now