[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


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; 

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 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; 

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; 

Questions with sql tag:

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 Export result set on Dbeaver to CSV 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? #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’ MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server IF EXISTS THEN 1 ELSE 2 How to add a boolean datatype column to an existing table in sql? Presto SQL - Converting a date string to date format What is the meaning of <> in mysql query? Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Convert timestamp to date in Oracle SQL #1292 - Incorrect date value: '0000-00-00' Postgresql tables exists, but getting "relation does not exist" when querying SQL query to check if a name begins and ends with a vowel Find the number of employees in each department - SQL Oracle Error in MySQL when setting default value for DATE or DATETIME Drop view if exists Could not find server 'server name' in sys.servers. SQL Server 2014 How to create a Date in SQL Server given the Day, Month and Year as Integers TypeError: tuple indices must be integers, not str Select Rows with id having even number SELECT list is not in GROUP BY clause and contains nonaggregated column IN vs ANY operator in PostgreSQL How to insert date values into table Error related to only_full_group_by when executing a query in MySql How to select the first row of each group? Connecting to Microsoft SQL server using Python eloquent laravel: How to get a row count from a ->get() How to execute raw queries with Laravel 5.1? In Oracle SQL: How do you insert the current date + time into a table? Extract number from string with Oracle function Rebuild all indexes in a Database SQL: Two select statements in one query DB2 SQL error sqlcode=-104 sqlstate=42601 What difference between the DATE, TIME, DATETIME, and TIMESTAMP Types How to run .sql file in Oracle SQL developer tool to import database? Concatenate columns in Apache Spark DataFrame How Stuff and 'For Xml Path' work in SQL Server? Fatal error: Call to a member function query() on null

Questions with sql-server tag:

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 SQL Server IF EXISTS THEN 1 ELSE 2 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to add a boolean datatype column to an existing table in sql? How to import an Excel file into SQL Server? How to use the COLLATE in a JOIN in SQL Server? Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Stored procedure with default parameters Drop view if exists Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object How to update large table with millions of rows in SQL Server? Could not find server 'server name' in sys.servers. SQL Server 2014 How to create a Date in SQL Server given the Day, Month and Year as Integers Select Rows with id having even number A connection was successfully established with the server, but then an error occurred during the login process. (Error Number: 233) SQL Server: Error converting data type nvarchar to numeric How to add LocalDB to Visual Studio 2015 Community's SQL Server Object Explorer? Using DISTINCT along with GROUP BY in SQL Server Rebuild all indexes in a Database How to generate Entity Relationship (ER) Diagram of a database using Microsoft SQL Server Management Studio? The target principal name is incorrect. Cannot generate SSPI context How Stuff and 'For Xml Path' work in SQL Server? How to view the roles and permissions granted to any database user in Azure SQL server instance? How do I create a local database inside of Microsoft SQL Server 2014? Format number as percent in MS SQL Server MSSQL Regular expression How to select all the columns of a table except one column? SQL count rows in a table EXEC sp_executesql with multiple parameters SQL Server : How to test if a string has only digit characters Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query Remove decimal values using SQL query How to drop all tables from a database with one SQL query? How to get last 7 days data from current datetime to last 7 days in sql server Get last 30 day records from today date in SQL Server Using Excel VBA to run SQL query No process is on the other end of the pipe (SQL Server 2012) How to subtract 30 days from the current date using SQL Server Calculate time difference in minutes in SQL Server How to join two tables by multiple columns in SQL? The database cannot be opened because it is version 782. This server supports version 706 and earlier. A downgrade path is not supported

Questions with database tag:

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 IN vs ANY operator in PostgreSQL How to execute raw queries with Laravel 5.1? How should I tackle --secure-file-priv in MySQL? How to use multiple databases in Laravel Why does Oracle not find oci.dll? How do I create a local database inside of Microsoft SQL Server 2014? How to change the default port of mysql from 3306 to 3360 Laravel 5 error SQLSTATE[HY000] [1045] Access denied for user 'homestead'@'localhost' (using password: YES) How to change Oracle default data pump directory to import dumpfile? Using COALESCE to handle NULL values in PostgreSQL What is Hash and Range Primary Key? How to get item count from DynamoDB? Find duplicate records in MongoDB Django Model() vs Model.objects.create() Mongodb: Failed to connect to 127.0.0.1:27017, reason: errno:10061 How can I rename column in laravel using migration? There is already an object named in the database How to list all databases in the mongo shell? Failed to connect to mysql at 127.0.0.1:3306 with user root access denied for user 'root'@'localhost'(using password:YES) Partial Dependency (Databases) Warning: mysqli_real_escape_string() expects exactly 2 parameters, 1 given... what I do wrong? ORA-01652: unable to extend temp segment by 128 in tablespace SYSTEM: How to extend? Laravel: Error [PDOException]: Could not Find Driver in PostgreSQL Warning: mysqli_connect(): (HY000/1045): Access denied for user 'username'@'localhost' (using password: YES) The backend version is not supported to design database diagrams or tables Difference between partition key, composite key and clustering key in Cassandra? Backup/Restore a dockerized PostgreSQL database Can't connect to MySQL server on '127.0.0.1' (10061) (2003) copy from one database to another using oracle sql developer - connection failed Best way to check for "empty or null value" ORA-12528: TNS Listener: all appropriate instances are blocking new connections. Instance "CLRExtProc", status UNKNOWN getting error HTTP Status 405 - HTTP method GET is not supported by this URL but not used `get` ever? How do I to insert data into an SQL table using C# as well as implement an upload function? Postgres - Transpose Rows to Columns How to update multiple columns in single update statement in DB2 How to increase MySQL connections(max_connections)? What are OLTP and OLAP. What is the difference between them? The ALTER TABLE statement conflicted with the FOREIGN KEY constraint Reset identity seed after deleting records in SQL Server How can I get all sequences in an Oracle database?

Questions with between tag:

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 SQL : BETWEEN vs <= and >= Mysql: Select all data between two dates Does MS SQL Server's "between" include the range boundaries?