How to format a date column into just the month?

David_2

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

I would need the final value in some sort of date datatype so that 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;

The above produces an error "Invalid value for Month"

Any ideas?

[Deleted User]

You may be able to use either the date_part or month functions.

=> select date_part('month', purchase_date) from pdate;
 date_part
-----------
         4
         1
(2 rows)

=> select month(purchase_date) from pdate;
 month
-------
     4
     1
(2 rows)

David_2

The problem with those methods is I end up with just a digit ie 1, but it would better to have the month spelled out ie "January", but still sort in date order, not text order.

Perhaps this feature does not yet exist?

[Deleted User]

Ah. There isn't a direct function for this but you can possibly do it with a sql macro that you wrap the result of the month or date_part functions with. Below is an abbreviated example, you'd just need to extend the case statement to cover all the months. You should be able to change the data type of the arg passed in the function or cast the data type on the return values if desired.

=> create function tomonthname(x int) return varchar(20) as begin return (case when (x = '1') then 'January' when (x = '2') then 'February' ELSE 'no month' end); end;
CREATE FUNCTION
=> select tomonthname(month(purchase_date)) from pdate;
 tomonthname
-------------
 January
 January
(2 rows)