Select all records 'younger' than 7 days

Question

I want to select all the records from [Orders] that have a [Submissiondate] less than 7 days.

I'm completely stumped. This is the query I'm executing:

SELECT * FROM [Orders] WHERE ([SubmissionDate] < @SubmissionDate)

Doesn't work.

Any advance? did you get the solution? – Luis Quijada Jul 06 '12 at 07:42 — Luis Quijada, Jul 06 '12 at 07:42

score 5 · Answer 1 · answered Jun 30 '12 at 22:29

If you mean you want rows with SubmissionDate between @SubmissionDate and @SubmissionDate - 7 days, then this is how I would implement that in Transact-SQL:

WHERE [SubmissionDate] BETWEEN DATEADD(DAY, -7, @SubmissionDate)
                           AND @SubmissionDate

Note that BETWEEN implies >= and <=. If you need strict inequalities, make it something like this:

WHERE [SubmissionDate] > DATEADD(DAY, -7, @SubmissionDate)
  AND [SubmissionDate] < @SubmissionDate

score 1 · Answer 2 · edited May 23 '17 at 12:12

1

Assuming that the sql parameter @SubmissionDate is the date (and time) now. You could use the following query that will return those [Orders] submitted within the last 7 days:

SELECT * FROM [Orders] WHERE ([SubmissionDate] >= DATEADD(DD, -7, DATEADD(dd, 0, DATEDIFF(dd, 0, @SubmissionDate))))

Two important remarks to this solution:

Time 'part' is being removed from @SubmissionDate.
As there is no 'Date To' restriction, do includes the [Orders] submitted 'today' (until the time the query is being executed).

The following code is just to get the date 'part' only of a date-time (extracted from this other SO thread).

DATEADD(dd, 0, DATEDIFF(dd, 0, @SubmissionDate))

edited May 23 '17 at 12:12

Community

1
1

answered Jul 01 '12 at 11:39

Luis Quijada

2,345
1
26
31

Good point about resetting the time part. But if you assume that `@SubmissionDate` represents the current date and time, then there's not very much point in using the argument, is there? You could just use `GETDATE()` or `CURRENT_TIMESTAMP` instead. – Andriy M Jul 01 '12 at 14:02
What I mean is, if the OP *is* using an argument, then it is likely (in my opinion) that the argument represents an *arbitrary* timestamp, not always the current one. – Andriy M Jul 01 '12 at 14:07
@AndriyM yes I see your point, maybe that's something the author can clarify, if it is intended that `@SubmissionDate` param. allocates the current date (and time). Well, I always try to avoid in the queries to use directly date and time provided by the db-server and always use parameters, so I did feel comfortable with the assumption that `@SubmissionDate` was meant to allocate the current date and time. – Luis Quijada Jul 01 '12 at 15:06

score 1 · Answer 3 · answered Sep 21 '17 at 08:25

1

Try this

SELECT * FROM [Orders] WHERE [submission_date] < NOW() - INTERVAL 7 DAY;

answered Sep 21 '17 at 08:25

Vikas Chauhan

1,276
15
23

score 0 · Answer 4 · answered Jun 30 '12 at 19:02

0

You can also try this DATEDIFF

SELECT * FROM [Orders] WHERE  datediff(d,SubmissionDate,GETDATE()) > 7

where GetDate() is today's date and the d is difference in days

select datediff(d,'2012/06/23',GETDATE())

should give you 7 because it's 7 days ago

answered Jun 30 '12 at 19:02

codingbiz

26,179
8
59
96

Select all records 'younger' than 7 days

4 Answers4