CODEWITHSUNDEEP
mysqlsql
user1419810
user1419810

Reputation: 846

My SQL to get String from Date

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?

Upvotes: 2

Views: 48

Answers (1)

John Woo
John Woo

Reputation: 263813

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.

Upvotes: 2

Related Questions