I want to find record by this week just declare current date. Example:
select datename(dw,getdate()) -- Example today is Friday "27-04-2012"
so how can i get date range
start on monday "23-04-2012" or sunday "22-04-2012" As @dateStart to
end on sunday "29-04-2012" or saturday "28-04-2012" As @dateEnd
then i can select query by
select * from table where date>=@dateStart AND date<=@dateEnd
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.
SELECT * FROM YourTable. WHERE [dateColumn] >DATEADD(day,1,'4/25/2022') AND [dateColumn] <= DATEADD(day,1,'4/26/2022')
To find the difference between dates, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument defines the part of the date/datetime in which you'd like to express the difference. Its value can be year , quarter , month , day , minute , etc.
Here are some helpful commands to get each day of the week
SELECT
DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -2) SatOfPreviousWeek,
DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -1) SunOfCurrentWeek,
DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) MondayOfCurrentWeek,
DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 1) TuesOfCurrentWeek,
DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 2) WedOfCurrentWeek,
DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 3) ThursOfCurrentWeek,
DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 4) FriOfCurrentWeek,
DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 5) SatOfCurrentWeek
you would then use these in your query to get the date range:
SELECT *
FROM yourTable
WHERE yourDate >= DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -1) -- Sunday
AND yourDate <= DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 5) -- Saturday
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