1

I have a date column in a table which I have to return weekday name from it while I join it with another table.

Right now I get it as an int WEEKDAY(a.vila_date) weekdag

SELECT years, software, vsnr, WEEKDAY(a.vila_date) weekdag,  a.vilatime
FROM  vila a, onta v

I want a result like Monday, Tuesday, Wednesday, Thursday, Friday. Considering 1 = Monday and 0 = Sunday

itro
  • 7,006
  • 27
  • 78
  • 121

1 Answers1

2

You can use the TO_CHAR() function to format a DATE or DATETIME value or column into various formats including the full or abbreviated weekday name.

Example:

SELECT TODAY today, 
       WEEKDAY(TODAY) wday_num, 
       TO_CHAR(TODAY, "%a") wday_short,
       TO_CHAR(TODAY, "%A") wday_full
FROM systables 
WHERE tabid = 1;

Output:

today       06/04/2021      
wday_num    5
wday_short  Fri
wday_full   Friday

The documentation for the TO_CHAR() function can be found at https://www.ibm.com/docs/en/informix-servers/12.10?topic=dcf-char-function#ids_sqt_129 where you can also search for information on the GL_DATETIME and GL_DATE environment variable which list the formatting directives (such as "%a") that can be used.

lemon
  • 14,875
  • 6
  • 18
  • 38
Simon Riddle
  • 976
  • 4
  • 4
  • Can we have it in local language also? I mean the weekday name in other languages – itro Jun 04 '21 at 10:01
  • 1
    The language used for weekday names, format of a date and various other things are defined by the setting of the CLIENT_LOCALE environment variable which needs to be compatible with the locale used for the database. If not set this will default to en_US.819 as in the example shown. – Simon Riddle Jun 04 '21 at 10:25
  • We don't have CLIENT_LOCALE environment variable and I have no access to add it. Is there a workaround to do it in function or in the query? – itro Jun 04 '21 at 11:24