Search code examples
sqldayofweek

Find closest last day of the week

I have rows with a date column. I need to find the values closest to the end of each week.

Example: For week 3 - 9 Jan, from the values 4,5,6 Jan, it will return 6 Jan, and for week 10 - 16 Jan, from the values 10,11 it will return 11 Jan.

So out of the rows containing 4,5,6,10,11 Jan, the query should return Thu 6 and Tue 11 Jan.

Hope this makes sense.

Solution

  • You need to state which RDBMS you are working with for a better answer.

    For SQL Server, you can use this

    select MAX(date)
from #tmp
group by DATEPART(wk, date)

    Note: the "week" runs from Sun-Sat unless you use SET DATEFIRST to change the first day of week.

    To run Mon-Sun, use this instead

    select MAX(date)
from #tmp
group by DATEPART(wk, date-1)

    Test table used

    create table #tmp (date datetime)
insert #tmp select '20110104'
insert #tmp select '20110105'
insert #tmp select '20110106'
insert #tmp select '20110110'
insert #tmp select '20110111'