davidjhp davidjhp - 4 months ago 42
SQL Question

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?

Answer

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);
Comments