Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get last week date range based on current date in sql?

I have this code in crystal reports that gives me last week date range based on the current date.

First day of the week:

If DayOfWeek(currentdate) = 2 Then
currentdate
Else If DayOfWeek(currentdate) = 3 Then
dateadd ("d",-1,currentdate)
Else If DayOfWeek(currentdate) = 4 Then
dateadd ("d",-2,currentdate)
Else If DayOfWeek(currentdate) = 5 Then
dateadd ("d",-3,currentdate)
Else If DayOfWeek(currentdate) = 6 Then
dateadd ("d",-4,currentdate)
Else If DayOfWeek(currentdate) = 7 Then
dateadd ("d",-5,currentdate)
Else If DayOfWeek(currentdate) = 1 Then
dateadd ("d",-6,currentdate)

Last day of week:

If DayOfWeek(currentdate) = 2 Then
dateadd ("d",+6,currentdate)
Else If DayOfWeek(currentdate) = 3 Then
dateadd ("d",+5,currentdate)
Else If DayOfWeek(currentdate) = 4 Then
dateadd ("d",+4,currentdate)
Else If DayOfWeek(currentdate) = 5 Then
dateadd ("d",+3,currentdate)
Else If DayOfWeek(currentdate) = 6 Then
dateadd ("d",+2,currentdate)
Else If DayOfWeek(currentdate) = 7 Then
dateadd ("d",+1,currentdate)
Else If DayOfWeek(currentdate) = 1 then currentdate

How can I do the same in SQL using 2 variables to storage Monday(startdate) and Sunday(enddate)?

I found this select datepart(dw,getdate()) --6 in this site, but I do not know how to use it.

like image 370
asfsdf Avatar asked Mar 31 '14 19:03

asfsdf


People also ask

How can I get last 7 days data in SQL?

We use system function now() to get the latest datetime value, and INTERVAL clause to calculate a date 7 days in the past.

How do I get current week data in SQL?

It is very easy to get current week data in MySQL. Here is the SQL query to get records of current week in MySQL. In the above query, we use now() function to get present date, and week() function to get week number of date values. So we select rows whose order_date's week number is same as week number of today's day.


1 Answers

I generated some spaced out dates in the parms CTE then SELECT the CurrentDate from parms, the Sunday of the week prior to CurrentDate and the Saturday of the week prior to CurrentDate. I'm assuming that you want the dtate range to be Sunday - Saturday.

Sunday - Saturday Ranges

;WITH parms (CurrentDate) AS (
    SELECT DATEADD(dd, -14, CURRENT_TIMESTAMP) UNION 
    SELECT DATEADD(dd, -6, CURRENT_TIMESTAMP) UNION
    SELECT DATEADD(dd, 2, CURRENT_TIMESTAMP) UNION
    SELECT DATEADD(dd, 8, CURRENT_TIMESTAMP) UNION
    SELECT DATEADD(dd, 15, CURRENT_TIMESTAMP) UNION
    SELECT DATEADD(dd, 20, CURRENT_TIMESTAMP)
)

SELECT CurrentDate
     , LastWeekSunday   = DATEADD(dd, -1, DATEADD(ww, DATEDIFF(ww, 0, CurrentDate) - 1, 0))
     , LastWeekSaturday = DATEADD(dd,  5, DATEADD(ww, DATEDIFF(ww, 0, CurrentDate) - 1, 0))
FROM parms

Monday to Sunday Ranges

;WITH parms (CurrentDate) AS (
    SELECT DATEADD(dd, -14, CURRENT_TIMESTAMP) UNION 
    SELECT DATEADD(dd, -6, CURRENT_TIMESTAMP) UNION
    SELECT DATEADD(dd, 2, CURRENT_TIMESTAMP) UNION
    SELECT DATEADD(dd, 8, CURRENT_TIMESTAMP) UNION
    SELECT DATEADD(dd, 15, CURRENT_TIMESTAMP) UNION
    SELECT DATEADD(dd, 20, CURRENT_TIMESTAMP)
)

SELECT CurrentDate
     , LastWeekMonday   = DATEADD(dd,  0, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, CurrentDate)) - 1, 0))
     , LastWeekSunday   = DATEADD(dd,  6, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, CurrentDate)) - 1, 0))
FROM parms

If you just want the prior week's Monday to the prior week's Sunday from today rather than from a column of dates you can use this

SELECT CURRENT_TIMESTAMP
     , LastWeekSunday   = DATEADD(dd,  0, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, CURRENT_TIMESTAMP)) - 1, 0))
     , LastWeekSaturday = DATEADD(dd,  6, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, CURRENT_TIMESTAMP)) - 1, 0))
like image 143
Bill Stidham Avatar answered Oct 24 '22 00:10

Bill Stidham