[sql] SQL Query Where Date = Today Minus 7 Days

I have a SQL table of hits to my website called ExternalHits. I track the URL as URLx and the date the page was accessed as Datex. I run this query every week to get the count of total hits from the week before, and every week I have to manually change the "between" dates. Is there some way I can change my query so that the "between" dates are something like TODAY AND TODAY-7? Ijust want to not have to manually change the dates every week.

    SELECT URLX, COUNT(URLx) AS Count
    FROM ExternalHits
    WHERE datex BETWEEN '02/27/2017' AND '03/05/2017'    
    GROUP BY URLx
    ORDER BY Count DESC; 

This question is related to sql sql-server database between

The answer is


Use the built in functions:

SELECT URLX, COUNT(URLx) AS Count
FROM ExternalHits
WHERE datex BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW()
GROUP BY URLx
ORDER BY Count DESC; 

Using dateadd to remove a week from the current date.

datex BETWEEN DATEADD(WEEK,-1,GETDATE()) AND GETDATE()

You can subtract 7 from the current date with this:

WHERE datex BETWEEN DATEADD(day, -7, GETDATE()) AND GETDATE()

Use the following:

WHERE datex BETWEEN GETDATE() AND DATEADD(DAY, -7, GETDATE())

Hope this helps.


DECLARE @Daysforward int
SELECT @Daysforward = 25 (no of days required)
Select * from table name

where CAST( columnDate AS date) < DATEADD(day,1+@Daysforward,CAST(GETDATE() AS date))

You can use the CURDATE() and DATE_SUB() functions to achieve this:

SELECT URLX, COUNT(URLx) AS Count
FROM ExternalHits
WHERE datex BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE()    
GROUP BY URLx
ORDER BY Count DESC; 

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to database

Implement specialization in ER diagram phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' cannot be loaded Room - Schema export directory is not provided to the annotation processor so we cannot export the schema SQL Query Where Date = Today Minus 7 Days MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server date format yyyymmdd How to create a foreign key in phpmyadmin WooCommerce: Finding the products in database TypeError: tuple indices must be integers, not str

Examples related to between

SQL Query Where Date = Today Minus 7 Days comparing elements of the same array in java Check if current date is between two dates Oracle SQL Postgresql query between date ranges Datetime BETWEEN statement not working in SQL Server Select entries between dates in doctrine 2 PHP: How can I determine if a variable has a value that is between two distinct constant values? MySQL "between" clause not inclusive? generate days from date range SQL Query NOT Between Two Dates