2

I'm trying to construct a string within an sql query from a date range to create another column to denote the Monday of the week the day belongs in, i.e. create the below:

salesDate   mondayText
13/11/17    Mon 13 Nov
14/11/17    Mon 13 Nov
15/11/17    Mon 13 Nov
16/11/17    Mon 13 Nov
17/11/17    Mon 13 Nov
18/11/17    Mon 13 Nov
19/11/17    Mon 13 Nov
20/11/17    Mon 20 Nov
21/11/17    Mon 20 Nov
22/11/17    Mon 20 Nov
23/11/17    Mon 20 Nov
24/11/17    Mon 20 Nov
25/11/17    Mon 20 Nov
26/11/17    Mon 20 Nov

I've tried the following but it seems a bit like overkill:

CONCAT(LEFT(DAYNAME(STR_TO_DATE(CONCAT(YEAR(`order_date`),LPAD(WEEKOFYEAR(`order_date`),2,'0'),' ','Monday'), '%X%V %W')),3),' ',
            EXTRACT(DAY FROM (`order_date` - INTERVAL WEEKDAY(`order_date`) Day)),' ',
    LEFT(MONTHNAME(STR_TO_DATE(CONCAT(YEAR(`order_date`),LPAD(WEEKOFYEAR(`order_date`),2,'0'),' ','Monday'), '%X%V %W')),3)) AS mondayText

An this causes problems when weeks span months. Anyone any ideas?

John Woo
  • 238,432
  • 61
  • 456
  • 464
user1419810
  • 818
  • 13
  • 27
  • Possible duplicate of [mysql STR\_TO\_DATE not working](https://stackoverflow.com/questions/25561169/mysql-str-to-date-not-working) – Shashwat Feb 08 '18 at 05:18
  • Quite the opposite, I'm trying to get the mondayText data from the date, not the other way around? – user1419810 Feb 08 '18 at 05:23

1 Answers1

2

Assuming that your salesDate column is string and has the following format - dd/MM/YY. You can simply make use of MySQL datetime functions,

  • DATE_FORMAT
  • STR_TO_DATE
  • SUBDATE
  • WEEKDAY

Example 

DATE_FORMAT(SUBDATE(STR_TO_DATE(salesDate,'%d/%m/%y'), WEEKDAY(STR_TO_DATE(salesDate,'%d/%m/%y'))), '%a %d %b')

Here's a Demo.

However, you don't have to use STR_TO_DATE if the column's datatype is already DATETIME.

Demo 2.

John Woo
  • 238,432
  • 61
  • 456
  • 464