How to Retrieve Past 7 Days\' Data in SQL Server with DATEADD Function?

Retrieving the Past 7 Days' Data in SQL Server

The task of efficiently obtaining the past 7 days' worth of data from a SQL Server table arises in numerous data management scenarios. One such situation involves loading data from a SQL Server table named "A" into a MySQL table using Pentaho. To ensure accuracy and relevance, it becomes necessary to filter the data such that only the data from the last 7 days is retrieved.

In the given context, the "CreatedDate" column in the SQL Server table "A" is defined as a "datetime" data type. However, in the MySQL table, the corresponding column "created_on" is defined as a "timestamp" data type. It's important to note that these data types have slight differences in their representation and precision.

The initial query provided by the user attempted to select the required data using the following statement:

SELECT id,    
NewsHeadline as news_headline,    
NewsText as news_text,    
state,    
CreatedDate as created_on      
FROM News    
WHERE CreatedDate BETWEEN GETDATE()-7 AND GETDATE()
order by createddate DESC

However, this query resulted in the retrieval of only 5 days' worth of data. To rectify this issue and accurately capture the past 7 days' data, we need to make a slight adjustment to the query.

Here's a modified query that will correctly select the desired data:

SELECT id, NewsHeadline as news_headline, NewsText as news_text, state CreatedDate as created_on
 FROM News 
 WHERE CreatedDate >= DATEADD(day,-7, GETDATE())

In this modified query, we use the DATEADD function to subtract 7 days from the current date and obtain a date value that represents the beginning of the past 7-day period. By specifying this as the lower bound of our date range, we ensure that the query retrieves all data that falls within the last 7 days.

The above is the detailed content of How to Retrieve Past 7 Days' Data in SQL Server with DATEADD Function?. For more information, please follow other related articles on the PHP Chinese website!