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?
How to format a date column into only the month.
Moderator: NorbertKrupa
Re: How to format a date column into only the month.
Did you try extract function?
SELECT EXTRACT(MONTH FROM purchase_date)
Sorry, I did not test it.
SELECT EXTRACT(MONTH FROM purchase_date)
Sorry, I did not test it.
Re: How to format a date column into only the month.
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?
Perhaps this feature does not yet exist?
-
- 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.
Last edited by NorbertKrupa on Thu Feb 06, 2014 2:36 pm, edited 1 time in total.
Checkout vertica.tips for more Vertica resources.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: How to format a date column into only the month.
davidjhp,
Are you ordering this somewhere other than in SQL? Why not just sort by the purchase_date column?
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
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
-
- 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.
Learned something new; thanks Jim.
Checkout vertica.tips for more Vertica resources.