Filtering for Dates that Belong to the Current Week with SQL

If you have a query that needs to filter the results by their date and this date should belong to the current week, you can do this with the SQL capabilities from Data Virtuality.

The following example shows how to use the functions TIMESTAMPADD(), DAYOFWEEK() and CURDATE() in order to dynamically find the first and the last day of the current week, provided that the week starts on Monday and ends on Sunday (to check your Data Virtuality Server's locale and change it, if necessary, please see this guide):

SELECT <resultSetColumn[]>
FROM   <sourceTable>
WHERE  <columnWithDate> between cast(timestampadd(SQL_TSI_DAY, 
-(dayofweek(curdate())-2), curdate()) as date) and
                                cast(timestampadd(SQL_TSI_DAY, 
7-(dayofweek(curdate())-1), curdate()) as date)