Search code examples
sqlvertica

How to format a Vertica date column into just the month?

I am trying to format a Vertica date column into only the month.

I would need the final value in some sort of date datatype so in the report I can order the results by date/time, not order by text. So that February sorts after January etc.

select TO_DATE(TO_CHAR(purchase_date), 'Month')  
from transactions
order by 1;

I am also tried:

select TO_DATE(TO_CHAR(MONTH(purchase_date)), 'Month')  
from transactions
order by 1;

The above statements produce an error "Invalid value for Month"

Any ideas?

Solution

  • How about this?

    select to_char(purchase_date, 'Month')
from transactions
order by purchase_date;

    You can order by columns that are not in the select list.

    EDIT:

    If you want to combine months from multiple years, the above will not work quite right. This will:

    select to_char(purchase_date, 'Month')
from transactions
order by extract(month from purchase_date);