Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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()
like image 671
sona Avatar asked Apr 14 '14 02:04

sona


People also ask

How do I get weekly week data in SQL?

WEEK() function in MySQL is used to find week number for a given date. If the date is NULL, the WEEK() function will return NULL. Otherwise, it returns the value of week which ranges between 0 to 53. The date or datetime from which we want to extract the week.

How do I get current week records in MySQL?

To query MySQL on the current week, you can use YEARWEEK() function.


4 Answers

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.
like image 140
Twinkles Avatar answered Oct 19 '22 06:10

Twinkles


A better way would be

select datepart(ww, getdate()) as CurrentWeek

You can also use wk instead of ww.

Datepart Documentation

like image 33
Henrik Erlandsson Avatar answered Oct 19 '22 06:10

Henrik Erlandsson


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

like image 2
Usama Avatar answered Oct 19 '22 07:10

Usama


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

like image 1
Z.D. Avatar answered Oct 19 '22 07:10

Z.D.