CODEWITHSUNDEEP
mysqlsqldatedatetime
Developer
Developer

Reputation: 847

Subtract one week from current week of the year

I am trying to get one week earlier then current week of the year but my sql query is returning null. here is my query

select date_sub(yearweek('2014-01-01'),INTERVAL 1 week)

what is wrong with this query

Upvotes: 2

Views: 3094

Answers (6)

Michael Kampmann Rasmussen
Michael Kampmann Rasmussen

Reputation: 148

SELECT * FROM [table] WHERE WEEKOFYEAR(date) = WEEKOFYEAR(NOW()) - 1;

Upvotes: 0

Dilip Chauhan
Dilip Chauhan

Reputation: 124

Are you looking for week number??? If yes then plz try this if it will work for you

Select DatePart(Week, Date add(day,-7,convert(date time,'01-jan-2014')))

Pleas let me know if you are looking for something else.

Upvotes: 0

GarethD
GarethD

Reputation: 69789

The problem is that DATE_SUB takes a date as the first arguement, but year week returns yyyyww i.e. not a date. So this:

SELECT yearweek('2014-01-01');

Returns 201352, this is then implicitly casted to a date, since it is not a date the result is null. You can replicate this by doing:

SELECT DATE(yearweek('2014-01-01'));

So if you subtract a week from NULL the result is also NULL.

The fix is to subtract the week first, then get the year week:

SELECT yearweek(date_sub('2014-01-01', INTERVAL 1 WEEK));

And the result is 201351.

Upvotes: 0

poncha
poncha

Reputation: 7866

If you want to get YEARWEEK of week prior to date, you can do this: Note: YEARWEEK results in 6-digit number, first 4 digits are week year, trailing 2 digits are week number.

SELECT YEARWEEK('2014-01-01' - INTERVAL 1 WEEK)

If you need to get a date that is one week before a given date, then:

SELECT '2014-01-01' - INTERVAL 1 WEEK

Upvotes: 3

Rahul Tripathi
Rahul Tripathi

Reputation: 172518

Try this:-

DATE_SUB(date('2014-01-01'), INTERVAL 7 DAY)

or

SELECT '2014-01-01' - INTERVAL 1 WEEK

Upvotes: 0

Sadikhasan
Sadikhasan

Reputation: 18600

Try this

select date_sub(date('2014-01-01'),INTERVAL 1 week)

Upvotes: 0

Related Questions