How to get current week dates in SQL Server?

-- Declare temporary variables @day, @today.
Declare @day INT, @today SMALLDATETIME
-- Assign values to temporary variables.
-- Now see the output for @today:
Select @today as [Today]

Note: Here iā€™m truncating the time part from current date.

Set @day = DATEPART(dw, @today)
-- Now see the output for @today:
Select @day as [Day]

Note: Here ā€˜4ā€™ means 4th day of current week. So it is Wednesday. But when you are euting this script, your output may or may not differ. If you got 3 as your output, means current day is Tuesday.

-- Declare temporary table '@WeekDateDay'.
Declare     @WeekDateDay Table ( DayName varchar(20), Date SMALLDATETIME )
-- Inser data into '@WeekDateDay' table.
Select 'Sunday', DATEADD(dd, 1 - @day, @today)
union all
Select 'Monday',DATEADD(dd, 2 - @day, @today)
union all
Select 'Tuesday',DATEADD(dd, 3 - @day, @today)
union all
Select 'Wednesday',DATEADD(dd, 4 - @day, @today)
union all
Select 'Thursday',DATEADD(dd, 5 - @day, @today)
union all
Select 'Friday',DATEADD(dd, 6 - @day, @today)
union all
Select 'Saturday',DATEADD(dd, 7 - @day, @today)
-- Now see the output for Current Week dates.
select * from @WeekDateDay

When to use

Example 1: If you want to display current week travelers list


-- Select Current Week Travelers List.
select  *
from    dbo.trnxTicket
where   DepartureDate in (select Date from @WeekDateDay)


Example 2: If you want to display current week interviews list


-- Select Current Week Interviewers List.
select  *
from    dbo.trnxInterview
where    InterViewDate in (select Date from @WeekDateDay)