Hi I have a table with a date field and some other information. I want to select all entries from the past week, (week start from Sunday).
table values:
id date
2 2011-05-14 09:17:25
5 2011-05-16 09:17:25
6 2011-05-17 09:17:25
8 2011-05-20 09:17:25
15 2011-05-22 09:17:25
I want to select all ids from last week, expected output is 5, 6, 8. (id 2 not in last week, and id 15 is in current week.)
How to write and SQL Query for the same.
A simple way can be this one, this is a real example from my code and works perfectly:
where("actions.created_at >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK)")
Try this:
Declare @Daytype varchar(15),
@StartDate datetime,
@EndDate datetime
set @Daytype = datename(dw, getdate())
if @Daytype= 'Monday'
begin
set @StartDate = getdate()-7
set @EndDate = getdate()-1
end
else if @Daytype = 'Tuesday'
begin
set @StartDate = getdate()-8
set @EndDate = getdate()-2
end
Else if @Daytype = 'Wednesday'
begin
set @StartDate = getdate()-9
set @EndDate = getdate()-3
end
Else if @Daytype = 'Thursday'
begin
set @StartDate = getdate()-10
set @EndDate = getdate()-4
end
Else if @Daytype = 'Friday'
begin
set @StartDate = getdate()-11
set @EndDate = getdate()-5
end
Else if @Daytype = 'Saturday'
begin
set @StartDate = getdate()-12
set @EndDate = getdate()-6
end
Else if @Daytype = 'Sunday'
begin
set @StartDate = getdate()-13
set @EndDate = getdate()-7
end
select @StartDate,@EndDate
SELECT id FROM tb1
WHERE
YEARWEEK (date) = YEARWEEK( current_date -interval 1 week )
You'll need to calc which day relative to today is Sunday in your middleware (php, python, etc.)*
Then,
select id
from table
where date >= "$sunday-date" + interval 7 DAY
i Use this for the week start from SUNDAY:
SELECT id FROM tbl
WHERE
date >= curdate() - INTERVAL DAYOFWEEK(curdate())+5 DAY
AND date < curdate() - INTERVAL DAYOFWEEK(curdate())-2 DAY
SELECT id FROM table1
WHERE YEARWEEK(date) = YEARWEEK(NOW() - INTERVAL 1 WEEK)
I use the YEARWEEK function specifically to go back to the prior whole calendar week (as opposed to 7 days before today). YEARWEEK also allows a second argument that will set the start of the week or determine how the first/last week of the year are handled. YEARWEEK lets you to keep the number of weeks to go back/forward in a single variable, and will not include the same week number from prior/future years, and it's far shorter than most of the other answers on here.
WHERE yourDateColumn > DATEADD(DAY, -7, GETDATE()) ;
The above query will not work.
After the where clause, if we can not CAST the column value, then it will not work. You should cast the column value.
e.g.:
SELECT.....
WHERE CAST( yourDateColumn AS DATE ) > DATEADD( DAY, -7, CAST( GETDATE() AS DATE )
PLEASE people... 'Last week' like the OP asked and where I was looking for (but found none of answers satisfying) is THE LAST WEEK.
If today is Tuesday, then LAST WEEK is Monday A WEEK AGO to Sunday A WEEK AGO.
So:
WHERE
WEEK(yourdate) = WEEK(NOW()) - 1
Or for ISO weeks:
WHERE
WEEK(yourdate, 3) = WEEK(NOW(), 3) - 1
Simplified form:
Last week data:
SELECT id FROM tbl
WHERE
WEEK (date) = WEEK( current_date ) - 1 AND YEAR( date) = YEAR( current_date );
2 weeks ago data:
SELECT id FROM tbl
WHERE
WEEK (date) = WEEK( current_date ) - 2 AND YEAR( date) = YEAR( current_date );
SQL Fiddle
Last Week
SELECT UserName, InsertTime
FROM tblaccounts
WHERE WEEK(InsertTime) = WEEK(NOW()) - 1;
Last Month
SELECT UserName, InsertTime
FROM tblaccounts
WHERE MONTH(InsertTime) = MONTH(NOW()) - 1;
Last YEAR
SELECT UserName, InsertTime
FROM tblaccounts
WHERE YEAR(InsertTime) = YEAR(NOW()) - 1;
You can also use it esay way
SELECT *
FROM inventory
WHERE YEARWEEK(`modify`, 1) = YEARWEEK(CURDATE(), 1)
I often do a quick "last week" check as well and the following tends to work well for me and includes today.
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = Getdate() - 7 /* Seven Days Earlier */
SET @EndDate = Getdate() /* Now */
SELECT id
FROM mytable
WHERE date BETWEEN @StartDate AND @Enddate
If you want this to NOT include today just subtract an extra day from the @EndDate. If I select these two variables today get
@StartDate 2015-11-16 16:34:05.347 /* Last Monday */
@EndDate 2015-11-23 16:34:05.347 /* This Monday */
If I wanted Sunday to Sunday I would have the following.
SET @StartDate = Getdate() - 8 /* Eight Days Earlier */
SET @EndDate = Getdate() - 1 /* Yesterday */
@StartDate 2015-11-15 16:34:05.347 /* Previous Sunday */
@EndDate 2015-11-22 16:34:05.347 /* Last Sunday */
You can make your calculation in php and then add it to your query:
$date = date('Y-m-d H:i:s',time()-(7*86400)); // 7 days ago
$sql = "SELECT * FROM table WHERE date <='$date' ";
now this will give the date for a week ago
Probably the most simple way would be:
SELECT id
FROM table
WHERE date >= current_date - 7
For 8 days (i.e. Monday - Monday)
If you already know the dates then you can simply use between, like this:
SELECT id
FROM `Mytable`
where MyDate BETWEEN "2011-05-15" AND "2011-05-21"
If you're looking to retrieve records within the last 7 days, you can use the snippet below:
SELECT date FROM table_name WHERE DATE(date) >= CURDATE() - INTERVAL 7 DAY;
select id from tbname
where date between date_sub(now(),INTERVAL 1 WEEK) and now();
For more example Like last month, last year, last 15 days, last 3 months
Fetch Last WEEK Record
Using the below MySQL query for fetching the last week records from the mysql database table.
SELECT name, created_at
FROM employees
WHERE
YEARWEEK(`created_at`, 1) = YEARWEEK( CURDATE() - INTERVAL 1 WEEK, 1)
It can be in a single line:
SELECT * FROM table WHERE Date BETWEEN (NOW() - INTERVAL 7 DAY) AND NOW()
Source: Stackoverflow.com