SQL Query Where Date = Today Minus 7 Days

SQL Query Where Date = Today Minus 7 Days

declare @lastweek datetime
declare @now datetime
set @now = getdate()
set @lastweek = dateadd(day,-7,@now)

SELECT URLX, COUNT(URLx) AS Count
FROM ExternalHits
WHERE datex BETWEEN @lastweek AND @now
GROUP BY URLx
ORDER BY Count DESC; 

SQL Select Records based on current date minus two days

you should try to use dateadd function

select * from orders where orderdate > dateadd(dd,-1,cast(getdate() as date))

Now, this may be exactly what you need but then you need to understand that by casting to date we remove the time part and effectively go back to the start of the day and a day behind it(-1) gives the start of yesterday.

How to subtract 30 days from the current date using SQL Server

You can convert it to datetime, and then use DATEADD(DAY, -30, date).

See here.

edit

I suspect many people are finding this question because they want to substract from current date (as is the title of the question, but not what OP intended). The comment of munyul below answers that question more specifically. Since comments are considered ethereal (may be deleted at any given point), I'll repeat it here:

DATEADD(DAY, -30, GETDATE())

SQL where Date from today minus and plus days

try,

WHERE CUS_joinDate BETWEEN DATEADD(mm,-1,GETDATE()) AND DATEADD(mm,1,GETDATE())

• SQLFiddle Demo

SQL Server, filter for max date and max date minus 7 days

So I ended up with the next code:

SELECT *
FROM table    
WHERE (timestamp >= CAST(DATEADD(DAY, - 1, GETDATE()) AS datetime)) AND (timestamp < CAST(GETDATE() AS DATETIME)) OR
                 (timestamp >= CAST(DATEADD(DAY, - 8, GETDATE()) AS datetime)) AND (timestamp < CAST(DATEADD(day, - 7, GETDATE()) AS DATETIME)) AND (Factor1 = 'Criteria1' OR
                 Factor2 = 'Criteria2')

Not sure if it's the best or the most elegant solution, but it works for me.

Query for results between now and 7 days from now

You can use logic like this:

where datefield >= dateadd(day, 1, cast(getdate() as date)) and
      datefield < dateadd(day, 8, cast(getdate() as date))

This assumes you are using SQL Server (based on your syntax) and that you don't want today's data ("next 7 days").

Subtract one day from datetime

Try this

SELECT DATEDIFF(DAY,  DATEADD(day, -1, '2013-03-13 00:00:00.000'), GETDATE())

OR

SELECT DATEDIFF(DAY,  DATEADD(day, -1, @CreatedDate), GETDATE())

---

Related Topics