2

I'm trying to solve a task: I have a table containing information about ships' battles. Battle is made of name and date. The problem is to get the last friday of the month when the battle occurred.

WITH num(n) AS(
SELECT 0 
UNION ALL
SELECT n+1 FROM num 
WHERE n < 31),
dat AS (
SELECT DATEADD(dd,  n,  CAST(battles.date AS DATE)) AS day, 
    dateadd(dd, 0, cast(battles.date as date)) as fight, 
    name FROM num,battles)
SELECT name, fight, max(day) FROM dat WHERE DATENAME(dw, day) = 'friday'

I thought there must be a maximum of date or something, but my code is wrong.

The result should look like this:

Please, help!!

P.S. DATE_FORMAT is not available

LSerni
  • 55,617
  • 10
  • 65
  • 107
messier101
  • 101
  • 7
  • possible duplicate of [Get the First or Last Friday in a Month](http://stackoverflow.com/questions/924246/get-the-first-or-last-friday-in-a-month) – JohnP Jan 22 '15 at 21:57
  • @JohnP that post is regarding PHP code, this one is pure MySql. Although the PHP option may be the easiest for this task. – Scriptable Jan 22 '15 at 21:59
  • I'm afraid I'm not strong in php, and in mysql also. And I can not use php – messier101 Jan 22 '15 at 22:00
  • Are you sure this query is for **MySQL**, and not for SQL Server or some other RDBMS? (If this is MySQL, what version of MySQL supports common table expressions?) – spencer7593 Jan 22 '15 at 22:57

1 Answers1

0

Possible problem: as spencer7593 noticed - and as I should have done and didn't - your original query is not MySQL at all. If you're porting a query that's OK. Otherwise this answer will not be helpful, as it makes use of MySQL functions.

The day you want is number 4 (0 being Sunday in MySQL).

So you want the last day of the month if the last day of the month is a 4; if the day of the month is a 5 you want a date which is 1 day earlier; if the day of the month is a 3 you want a date which is 1 day later, but that's impossible (the month ends), so you really need a date six days earlier.

This means that if the daynumber difference is negative, you want it modulo seven.

You can then build this expression (@DATE is your date; I use a fake date for testing)

SET @DATE='2015-02-18';
DATE_SUB(LAST_DAY(@DATE), INTERVAL ((WEEKDAY(LAST_DAY(@DATE))+7-4))%7 DAY);

It takes the last day of the month (LASTDAY(@DATE)), then it computes its weekday, getting a number from 0 to 6. Adds seven to ensure positivity after subtracting; then subtract the desired daynumber, in this case 4 for Friday.

The result, modulo seven, is the difference (always positive) from the last day's daynumber to the wanted daynumber. Since DATE_SUB(date, 0) returns the argument date, we needn't use IF.

SET @DATE='1962-10-20';
SELECT DATE_SUB(LAST_DAY(@DATE), INTERVAL ((WEEKDAY(LAST_DAY(@DATE))+7-4))%7 DAY) AS friday;
+------------+
| friday     |
+------------+
| 1962-10-26 |
+------------+

Your query then would become something like:

SELECT `name`, `date`,
    DATE_SUB(LAST_DAY(`date`),
       INTERVAL ((WEEKDAY(LAST_DAY(`date`))+7-4))%7 DAY) AS friday
FROM battles;
LSerni
  • 55,617
  • 10
  • 65
  • 107
  • @Len_D, your answer is the same as mine and you replied before me. Why did you delete your answer? As far as I can see it is correct. – LSerni Jan 22 '15 at 22:26
  • @spencer7593, my own MySQL (5.6.17 on Linux x64) gives 4 for today. `weekday(now()), date_format(now(), '%W')` returns [ 4, 'Friday' ]. – LSerni Jan 22 '15 at 23:21
  • Which is not to say that I was right in writing *0 to 7* - it was actually 0 to **6**. I edited the answer. Thanks. – LSerni Jan 22 '15 at 23:43
  • @Iserni: My mistake, I was mistakenly thinking that `WEEKDAY` was a synonym for the MySQL `DAYOFWEEK` function. But it isn't. `WEEKDAY` returns 0 through 6, `DAYOFWEEK` (which is the function I use) returns 1 through 7. – spencer7593 Jan 23 '15 at 03:16