CODEWITHSUNDEEP
sql-serversql-server-2008date
sona
sona

Reputation: 1552

how to get data of current week only in SQL server?

I want records from table which stores the current date when a record is inserted with in current week only.

I have tried:

SELECT PId 
,WorkDate 
,Hours          
,EmpId            
FROM Acb       
WHERE EmpId=@EmpId AND WorkDate BETWEEN DATEADD(DAY, -7, GETDATE()) AND GETDATE()

Upvotes: 31

Views: 128772

Answers (7)

dotnetdummy
dotnetdummy

Reputation: 193

Using DATEDIFF works as well, however a bit hacky since it doesn't care about datefirst:

set datefirst 1; -- set monday as first day of week
declare @Now datetime = '2020-09-28 11:00';


select * 
into #Temp
from
(select 1 as Nbr, '2020-09-22 10:00' as Created
union
select 2 as Nbr, '2020-09-25 10:00' as Created
union
select 2 as Nbr, '2020-09-28 10:00' as Created) t


select * from #Temp where DATEDIFF(ww, dateadd(dd, -@@datefirst, Created), dateadd(dd, -@@datefirst, @Now)) = 0 -- returns 1 result
select * from #Temp where DATEDIFF(ww, dateadd(dd, -@@datefirst, Created), dateadd(dd, -@@datefirst, @Now)) = 1 -- returns 2 results


drop table #Temp

Upvotes: 0

Navnath jagdale
Navnath jagdale

Reputation: 1

SET DATEFIRST 1;
    ;With CTE
    AS
    (
    SELECT
    FORMAT(CreatedDate, 'MMMM-yyyy') as Months,
    CASE 
    WHEN YEAR(DATEADD(DAY, 1-DATEPART(WEEKDAY, Min(CreatedDate)), Min(CreatedDate))) < YEAR(Min(CreatedDate))
    THEN FORMAT(DATEADD(YEAR, DATEDIFF(YEAR, 0,DATEADD(YEAR, 0 ,GETDATE())), 0) ,'MMM dd')   + ' - ' + FORMAT(DATEADD(dd, 7-(DATEPART(dw, Min(CreatedDate))), Min(CreatedDate))  ,'MMM dd')
    ELSE
    FORMAT(DATEADD(DAY, 1-DATEPART(WEEKDAY, Min(CreatedDate)), Min(CreatedDate)) ,'MMM dd') + ' - ' + FORMAT(DATEADD(dd, 7-(DATEPART(dw, Min(CreatedDate))), Min(CreatedDate)) ,'MMM dd') 
    END  DateRange,
    Sum(ISNULL(Total,0)) AS Total,
    sum(cast(Duration as int)) as   Duration    
    FROM TL_VriandOPI_Vendorbilling where VendorId=@userID and CompanyId=@CompanyID
    Group By DATEPART(wk, CreatedDate) ,FORMAT(CreatedDate, 'MMMM-yyyy')
    )
    SELECT Months,DateRange,Total,Duration,
    case when DateRange=(select FORMAT(DATEADD(DAY, 1-DATEPART(WEEKDAY, Min(getdate())), Min(getdate())) ,'MMM dd') + ' - ' +
    FORMAT(DATEADD(dd, 7-(DATEPART(dw, Min(getdate()))), Min(getdate())) ,'MMM dd'))
    then 1 else 0 end as Thisweek
    FROM CTE order by Months desc

Upvotes: 0

Usama
Usama

Reputation: 95

Its Working For Me.

Select * From Acb Where WorkDate BETWEEN DATEADD(DAY, -7, GETDATE()) AND DATEADD(DAY, 1, GETDATE())

You have to put this line After the AND Clause AND DATEADD(DAY, 1, GETDATE())

Upvotes: 2

Twinkles
Twinkles

Reputation: 1994

Do it like this:

SET DATEFIRST 1 -- Define beginning of week as Monday
SELECT [...]
AND WorkDate >= dateadd(day, 1-datepart(dw, getdate()), CONVERT(date,getdate())) 
AND WorkDate <  dateadd(day, 8-datepart(dw, getdate()), CONVERT(date,getdate()))

Explanation:

  • datepart(dw, getdate()) will return the number of the day in the current week, from 1 to 7, starting with whatever you specified using SET DATEFIRST.
  • dateadd(day, 1-datepart(dw, getdate()), getdate()) subtracts the necessary number of days to reach the beginning of the current week
  • CONVERT(date,getdate()) is used to remove the time portion of GETDATE(), because you want data beginning at midnight.

Upvotes: 47

Henrik Erlandsson
Henrik Erlandsson

Reputation: 3831

A better way would be 

select datepart(ww, getdate()) as CurrentWeek

You can also use wk instead of ww.

Datepart Documentation

Upvotes: 11

Amul Harad
Amul Harad

Reputation: 146

You can use following query to extract current week:

select datepart(dw, getdate()) as CurrentWeek

Upvotes: 0

Z .
Z .

Reputation: 12837

datepart(dw, getdate()) is the current day of the week, dateadd(day, 1-datepart(dw, getdate()), getdate()) should be the first day of the week, add 7 to it to get the last day of the week

Upvotes: 1

Related Questions