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()
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.
To query MySQL on the current week, you can use YEARWEEK() function.
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 weekCONVERT(date,getdate())
is used to remove the time portion of GETDATE(), because you want data beginning at midnight.A better way would be
select datepart(ww, getdate()) as CurrentWeek
You can also use wk instead of ww.
Datepart Documentation
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())
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With