First day of the month function?

Moderator: NorbertKrupa

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: First day of the month function?

Post by id10t » Wed Oct 09, 2013 2:32 pm

Hi!

[DELETED]
Last edited by id10t on Sat May 09, 2015 3:33 pm, edited 1 time in total.

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

Re: First day of the month function?

Post by JimKnicely » Wed Oct 09, 2013 3:17 pm

I was going to say the same thing!
Jim Knicely

Image

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

Vertica4VK
Newbie
Newbie
Posts: 6
Joined: Fri Aug 22, 2014 2:10 am

Re: First day of the month function?

Post by Vertica4VK » Fri Mar 06, 2015 2:28 am

Hello Jim

I have similar question - trying to figure out first day of month from a table which has a date column - I have gone through previous posts - but i get below error - i tried

dbadmin=> select to_date('my_rpt_mth_dt', 'YYYY-MM-01') from dev_data_monthly
ERROR 2005: Invalid input for YYYY: "my_rpt_mth_dt"

( i figured out another solution - but want to know if - vertica has any inbuilt formatting of date function... so i can use in above situation)

Thanks

VK

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

Re: First day of the month function?

Post by JimKnicely » Fri Mar 06, 2015 1:15 pm

Hi VK,

Instead of using the single quotes surrounding the my_rpt_mth_dt column, try the TO_CHAR function.

Code: Select all

dbadmin=> select my_rpt_mth_dt,  to_date(to_char(my_rpt_mth_dt), 'YYYY-MM-01') from dev_data_monthly;
 my_rpt_mth_dt |  to_date
---------------+------------
 2015-03-06    | 2015-03-01
(1 row)
Jim Knicely

Image

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

Post Reply

Return to “Vertica SQL Functions”