Search code examples
sqldatabasevertica

Convert day of week name to day of week number Vertica

  • With this way - SELECT TO_CHAR(NOW(), 'DAY'); I can get a day of week name(E.G. MONDAY).
  • With SELECT dayofweek(NOW()); I can get the number of the day of week. (E.G 1)
  • QUESTION: How to convert day of week name to day of week number. (E.G MONDAY -> 1) SMTH like TO_NUMBER('MONDAY', 'DAY'); --> 1 (just for example, don't working)
Solution

  • Probably a case expression is the simplest method:

    (case ? 
    when 'MONDAY' then 1
    when 'TUESDAY' then 2
    when 'WEDNESDAY' then 3
    when 'THURSDAY' then 4
    when 'FRIDAY' then 5
    when 'SATDAY' then 6
    when 'SUNDAY' then 7
 end)

    Or arrays are more concise:

    array_find(?,
           array['MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', 'SUNDAY']
          ) + 1