3

I have the following table:

store | dow |  turnover
------+-----+-----------
 1    |   1 | Eu59.426,00
 1    |   2 | Eu33.074,00
 1    |   5 | Eu38.855,00
 1    |   6 | Eu64.431,00

Please, tell me how to represent days of the week as Sunday, Monday etc. so that I don't have to create a new table with this mapping.

Ina
  • 187
  • 2
  • 9

2 Answers2

5

Try this: 

SELECT store, (CASE dow WHEN 1 THEN 'Sunday' 
                        WHEN 2 THEN 'Monday' 
                        WHEN 3 THEN 'Tuesday' 
                        WHEN 4 THEN 'Wednesday' 
                        WHEN 5 THEN 'Thursday' 
                        WHEN 6 THEN 'Friday'  
                        WHEN 7 THEN 'Saturday' 
              END), turnover 
FROM tableA;
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
  • 1
    To expand on this answer, you can use a CASE statement to supply your own custom day of week names, if you wish. For example: `SELECT store, CASE dow WHEN 1 THEN 'Sun' WHEN 2 THEN 'Mon' WHEN 3 THEN 'Tue' ... END, turnover FROM tableA` – Dan Dec 19 '13 at 11:26
  • @Saharsh Shah, doesnt work :( No function matches the given name and argument types. You might need to add explicit type casts. – Ina Dec 19 '13 at 11:27
  • 1
    in DAYNAME(dow)... dow must be in date format ,, but here you provide only index , how will it return day name ? – uvais Dec 19 '13 at 11:32
  • Is there any other way to do it? – Ina Dec 19 '13 at 11:34
1

try this :

SELECT store, 
       CASE dow 
       WHEN 1 THEN 'Sunday' 
       WHEN 2 THEN 'Monday' 
       WHEN 3 THEN 'Tuesday' 
       WHEN 4 THEN 'Wednesday' 
       WHEN 5 THEN 'Thursday' 
       WHEN 6 THEN 'Friday'  
       WHEN 7 THEN 'Saturday' 
     END, turnover 
FROM <tablename>;

note: in mysql i think there is no function which can return day name on index value,, DAYNAME('date')-- this is call only for obtaining a dayname for any date;

uvais
  • 416
  • 2
  • 6