How to Get Last Day of Last Week in SQL

How to get last day of last week in sql?

Regardless of the actual DATEFIRST setting, the last Sunday could be found like this:

SELECT DATEADD(day,
               -1 - (DATEPART(weekday, GETDATE()) + @@DATEFIRST - 2) % 7,
               GETDATE()
              ) AS LastSunday

Replace GETDATE() with a parameter @date to get the last Sunday before a particular date.

How to get first day of the week and last day of the week in sql server 2008?

Here's how you can do it:

DECLARE @yourdate date = getdate()
Select dateadd(ww, datediff(ww, 0, @yourdate), 0)
Select dateadd(ww, datediff(ww, 0, @yourdate), 4)

You set @yourdate to the date you want. The first SELECT will give you the first day and the second SELECT will give you the last date

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

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

MySQL - First & Last Day of Previous Week

Selecting first and last day of previous week:

SELECT
 (curdate() - INTERVAL((WEEKDAY(curdate()))+1) DAY) as e,
 (curdate() - INTERVAL((WEEKDAY(curdate()))+7) DAY) as s

s: 2017-02-20

e: 2017-02-26

And if you want filter last week table rows:

SELECT * FROM tblName
WHERE dateCol >= (curdate() - INTERVAL((WEEKDAY(curdate()))+7) DAY)
  AND dateCol < (curdate() - INTERVAL((WEEKDAY(curdate()))+1) DAY)
ORDER BY dateCol DESC -- or ASC

Find first date of previous week in SQL Server

Here's some handy ways of calculating First/Last day of This/Last week respecting DATEFIRST that I blogged a couple of weeks ago.

SET DATEFIRST 5 --Friday
-- Start/End of Weeks respecting DATEFIRST
SELECT DATEADD(DAY,  1-DATEPART(WEEKDAY, GETDATE()), GETDATE()) 'First Day of Current Week (DATEFIRST)';
SELECT DATEADD(DAY,  7-DATEPART(WEEKDAY, GETDATE()), GETDATE()) 'Last Day of Current Week (DATEFIRST)';
SELECT DATEADD(DAY, -6-DATEPART(WEEKDAY, GETDATE()), GETDATE()) 'First Day of Last Week (DATEFIRST)';
SELECT DATEADD(DAY,  0-DATEPART(WEEKDAY, GETDATE()), GETDATE()) 'Last Day of Last Week (DATEFIRST)';

(Gratuitous blog promo https://www.rednotebluenote.com/2016/04/first-day-of-the-week-and-datefirst/ )

MySQL Query to select data from last week?

SELECT id FROM tbl
WHERE date >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
AND date < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY

How to select last one week data from today's date

Yes, the syntax is accurate and it should be fine.

Here is the SQL Fiddle Demo I created for your particular case

create table sample2
(
    id int primary key,
    created_date date,
    data varchar(10)
  )

insert into sample2 values (1,'2012-01-01','testing');

And here is how to select the data

SELECT Created_Date
FROM sample2
WHERE Created_Date >= DATEADD(day,-11117, GETDATE())

---

Related Topics