CODEWITHSUNDEEP
sqlsql-serverdate
Yan Susanto
Yan Susanto

Reputation: 353

How get dates on week day?

I wanted to ask about how to get date on weekday

enter image description here

green is weekday

red is sunday

so when i input sql command it (like 27) when year 2012 it will show date 2012-07-2 until 2012-07-08

Upvotes: 0

Views: 140

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

This query uses a single parameter @weekno as input and returns the 7 days in that week, taking Monday as the first day of week. The definition of WeekNo does not follow SQL Server's DatePart(Week) because that depends on @@Datefirst. This doesn't.

The dateadd.. line is an expression that returns the first Monday of the year. I got it from here. The line above it just adds the weeks to it and 0-6 to create 7 days. To verify this is correct for any year, change CURRENT_TIMESTAMP in the query to a date, such as 20180708. FYI, 1-Jan-2018 is a Monday.

declare @weekno int = 27;
select
    (@weekno-1)*7+v.num+
    dateadd(dd,(datediff(dd,0,dateadd(yy,datediff(yy,0,CURRENT_TIMESTAMP),6))/7)*7,0)
from (values(0),(1),(2),(3),(4),(5),(6))v(num)
order by num

-- results
July, 02 2012 00:00:00+0000
July, 03 2012 00:00:00+0000
July, 04 2012 00:00:00+0000
July, 05 2012 00:00:00+0000
July, 06 2012 00:00:00+0000
July, 07 2012 00:00:00+0000
July, 08 2012 00:00:00+0000

Upvotes: 2

Related Questions