Retrieving Last 7 Days' Data in SQL Server

When loading data from an SQL Server table into a MySQL table, you may encounter the need to filter the data based on a time range. To retrieve the last seven days' worth of data, you can utilize the power of SQL Server's date manipulation functions.

Query:

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

Explanation:

• DATEADD: This function allows you to add or subtract a specified number of days from a given date. In this case, DATEADD(day,-7, GETDATE()) subtracts seven days from the current date, providing the starting point for the time range.
• GETDATE(): This function returns the current date and time in the SQL Server system.
• >=: The greater than or equal to operator ensures that all rows with CreatedDate values within or after the last seven days are included in the selection.
• ORDER BY: Finally, the query is ordered in descending order based on CreatedDate, allowing you to retrieve the most recent entries first.

Note:

• Verify that the data type of the CreatedDate column in the SQL Server table is compatible with the created_on column in the MySQL table.
• Ensure that the MySQL connection parameters are correctly configured in Pentaho before attempting the data load.