asfsdf - 2 years ago 150
SQL Question

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

I have this code in crystal reports that give me last week date range based on 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
Else If DayOfWeek(currentdate) = 3 Then
Else If DayOfWeek(currentdate) = 4 Then
Else If DayOfWeek(currentdate) = 5 Then
Else If DayOfWeek(currentdate) = 6 Then
Else If DayOfWeek(currentdate) = 7 Then
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.

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 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 CurrentDate
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