How to format a date column into only the month.

Moderator: NorbertKrupa

Post Reply
davidjhp
Newbie
Newbie
Posts: 3
Joined: Wed Feb 05, 2014 6:59 pm

How to format a date column into only the month.

Post by davidjhp » Wed Feb 05, 2014 11:12 pm

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

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

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.

The above query produces an error "Invalid value for Month"

Any ideas?

Mrao
Newbie
Newbie
Posts: 22
Joined: Mon Feb 03, 2014 2:07 pm

Re: How to format a date column into only the month.

Post by Mrao » Wed Feb 05, 2014 11:29 pm

Did you try extract function?

SELECT EXTRACT(MONTH FROM purchase_date)

Sorry, I did not test it.

davidjhp
Newbie
Newbie
Posts: 3
Joined: Wed Feb 05, 2014 6:59 pm

Re: How to format a date column into only the month.

Post by davidjhp » Wed Feb 05, 2014 11:38 pm

The problem with that method 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?

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: How to format a date column into only the month.

Post by NorbertKrupa » Thu Feb 06, 2014 1:13 am

Last edited by NorbertKrupa on Thu Feb 06, 2014 2:36 pm, edited 1 time in total.
Checkout vertica.tips for more Vertica resources.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: How to format a date column into only the month.

Post by JimKnicely » Thu Feb 06, 2014 5:27 am

davidjhp,

Are you ordering this somewhere other than in SQL? Why not just sort by the purchase_date column?

Code: Select all

dbadmin=> select * from transactions;
 purchase_date 
---------------
 2014-06-15
 2014-12-12
 2014-02-09
 2014-01-27
(4 rows)

dbadmin=> select TO_CHAR(purchase_date, 'Month')
dbadmin-> from transactions
dbadmin-> order by purchase_date;
  TO_CHAR  
-----------
 January  
 February 
 June     
 December 
(4 rows)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: How to format a date column into only the month.

Post by NorbertKrupa » Thu Feb 06, 2014 2:37 pm

Learned something new; thanks Jim.
Checkout vertica.tips for more Vertica resources.

Post Reply

Return to “New to Vertica SQL”