How to change a date column to show only month and year

Tools & Utilities
Enthusiast

How to change a date column to show only month and year

Hello,

I have a date column in my table reporting_date (date format 'YYYY-MM-DD')
I would like to change the column so I just show the month and year, I do not want the actual day of the month.

I have tried this

select income_month (format 'yyyy-mm'), income from D_CFAEISDB.FTP_INCOME

but it is not working. When I use this statement I get the same date I had dd/mm/yyyy

Any ideas of how can I get YYYY-MM or MM/YYYY?

Thanks in advanced, Maria
8 REPLIES
Enthusiast

Re: How to change a date column to show only month and year

Hi,
The sql you've specified, if run in BTEQ can get us the perfect result i.e column in the BTEQ export report w/ date in YYYY-MM format, But I believe you are trying to get the same result in SQL assistant. For SQL assistant you probably do a cast to char and then substring the result to get the column value in YYYY-MM format. Something like below select statement

select substr(cast (brth_dt as char(10)),1,7) from sushil.nayak

Hope this is what you were looking for. Thanks!
Enthusiast

Re: How to change a date column to show only month and year

That is brilliant!!! Exactly what I was after!!
Thanks so much, you can't imagine how much pain you have save me!!
Maria
Junior Contributor

Re: How to change a date column to show only month and year

Hi Maria,
you can use BTEQ's FORMAT, you just have to add a cast to CHAR:

select income_month (format 'yyyy-mm') (char(7))

Dieter
Enthusiast

Re: How to change a date column to show only month and year

Hi Dieter,

That works as well, thanks.
After a few more steps, I have now need to do something I am not sure I can.
in my table I have three main columns:

1. Income (decimal)
2. Income_month (income_month (format 'yyyy-mm') (char(7)))
3. Complete_open (date)

I need a select statement like this:

Select income, income_month where income_month between add_months(complete_open, 12) and add_months(complete_open, -12)

This is not working because the between just works with dates adn Income_month is not a date anymore.

Do you know any work around this?

thanks a lot!!

Re: How to change a date column to show only month and year

Hi,

I am facing the same issue, Want to compare the dates in BETWEEN.

But once the cast to Char is done, the comparision happens as a Character... any solution ... ?

Here is the code ..

cast(reporting_date as date format 'mmyyyy') between
cast(reporting_date as date format 'mmyyyy') and
cast(ADD_MONTHS (reporting_date, -4) as date format 'mmyyyy')

Thanks !
Junior Contributor

Re: How to change a date column to show only month and year

Of course you could simly change the FORMAT to 'yyyymm', but why do you cast to a char instead of using native DATEs?

DATE - (EXTRACT(DAY FROM DATE)-1) returns the first day of a month.

Re: How to change a date column to show only month and year

Nice formula. Thank you.

I think you meant 

DATE - (EXTRACT(DAY FROM DATE)+1)

Junior Contributor

Re: How to change a date column to show only month and year

Running your code you will notice that it return the next to last day of the previous month.

You probably meant DATE - EXTRACT(DAY FROM DATE)+1, but this is a bit less efficient (two time date arithmetric vs. one date plus one int arithmetric).

Additionally it would fail in the first week of year 1 :-)