SQL Server: How to get current week dates

 SQL Server     sbo     Updated on Jul 10, 2013     VIKRAM     1183   

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

  Related Articles
SQL Server: Non Equi Join
SQL Server: Equi Join
SQL Server: Cross Joins
SQL Server: Full Join (or) Full Outer Join
SQL Server: Joins
SQL Server: Right Join (or) Right Outer Join
SQL Server: Left Join (or) Left Outer Join
SQL Server: Inner Join (or) Join
SQL Server: Joins - Cartesian Product
  Recent Articles
SQL Server: Non Equi Join
SQL Server: Equi Join
SQL Server: Cross Joins
SQL Server: Full Join (or) Full Outer Join
SQL Server: Joins
SQL Server: Right Join (or) Right Outer Join
SQL Server: Left Join (or) Left Outer Join
SQL Server: Inner Join (or) Join
SQL Server: Joins - Cartesian Product