Get Previous Monday & Sunday's Date with SQL - Today's Date Calculation

If you are working with dates in SQL, you may need to calculate the previous Monday and Sunday's date based on the current date. This is a common task in many business applications, such as calculating weekly sales or generating weekly reports.

├Źndice
  1. Using SQL Date Functions
  2. Using Variables in SQL
  3. Conclusion

Using SQL Date Functions

To get the previous Monday's date, you can use the DATEADD function in SQL. This function adds a specified number of intervals to a date, in this case, we will add a negative number of days to get the previous Monday.

<code>SELECT DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0) - 7 AS PreviousMonday</code>

The above SQL statement will return the date of the previous Monday based on the current date. The DATEADD function adds a negative number of days (7) to the start of the current week (0).

To get the previous Sunday's date, you can use a similar SQL statement but add a different number of days. Here is an example:

<code>SELECT DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0) - 1 AS PreviousSunday</code>

Again, the DATEADD function is used to subtract a specific number of days (1) from the start of the current week to get the previous Sunday.

Using Variables in SQL

If you want to use variables in your SQL statement, you can modify the above code like this:

<code>DECLARE @Today DATE;
SET @Today = GETDATE();

SELECT DATEADD(wk, DATEDIFF(wk, 0, @Today), 0) - 7 AS PreviousMonday,
       DATEADD(wk, DATEDIFF(wk, 0, @Today), 0) - 1 AS PreviousSunday</code>

This SQL statement declares a variable called @Today and sets its value to the current date using the GETDATE function. Then, the DATEADD function is used to calculate the previous Monday and Sunday's date based on the value of @Today.

Conclusion

In conclusion, getting the previous Monday and Sunday's date with SQL is a simple task that can be accomplished using the DATEADD function and other SQL date functions. By using variables, you can make your SQL code more flexible and reusable in different contexts.

Click to rate this post!
[Total: 0 Average: 0]

Related posts

Leave a Reply

Your email address will not be published. Required fields are marked *

Go up

Below we inform you of the use we make of the data we collect while browsing our pages. You can change your preferences at any time by accessing the link to the Privacy Area that you will find at the bottom of our main page. More Information