How to get current week dates in SQL Server?

-- Declare temporary variables @day, @today.
Declare @day INT, @today SMALLDATETIME
-- Assign values to temporary variables.
Set @today = CAST(CONVERT(VARCHAR(10), GETDATE(), 101) AS SMALLDATETIME)
-- 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 4^th 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.
INSERT INTO @WeekDateDay
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

Query:

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

Query:

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