Year and Month only field

Database
Enthusiast

Year and Month only field

Could someone share some idea on how to create a 'YEAR/MONTH' only field in a teradata table? I tried it using the option 'TEST_DT DATE FORMAT 'YYYY/MM' ', but the 'DAY' portion still gets stored as '01' by default!! -Thanks
11 REPLIES
Enthusiast

Re: Year and Month only field

select substr(current_date, 1, 7) year_mo
2006-05

select substr(current_date (format 'yyyy/mm'), 1, 7) year_mo
2006/05

This will be a string.

will this work?

Enthusiast

Re: Year and Month only field

But the issue I am facing is while CREATING a table with 'Year/Month (YYYY/MM)' only field. Or in other words, it is possible to create a 'YEAR and MONTH only date field' in a Teradata table ? - Maku.
Enthusiast

Re: Year and Month only field

I am not sure if you can create a date field that will have only year and month yyyy/mm. (I never did that). If you create a date field it looks for a numeric value that convers to a valid date.
sorry !

Enthusiast

Re: Year and Month only field

You can define your column as INTERVAL type, example below: -

Create Table xyz
( ...
,cre_month interval year(4) to month
,...
);

Insert into xyz
( ...
,cre_month
,...
)
values
( ...
,'2006-02'
,...
);

Hope this helps.
Enthusiast

Re: Year and Month only field

Thanks..that works.
Do you know how to extract 'Year' or 'Month' out of this date field ?
Looks like regular EXTRACT function doesn't work with this.
Enthusiast

Re: Year and Month only field

Please write down your requirement for this field, how/where you want to use this field. There can be many solutions and we can find the best solution best on requirements.

Thanks.
Enthusiast

Re: Year and Month only field

Thanks Sunil..

The requirement is to have a Date field with only Month and Year data (YYYY/MM) and to retrieve it in the same format.

I know that it can be done in Teradata as follows:

Create table
(
Test_dt DATE FORMAT 'YYYY/MM'
)

But, while retrieving it using Sql Assistant, ODBC formats it to (MM/DD/YYYY) and defaults the day field to '01'. Any idea how to disable this and get the actual result out?

Btw: BTEQ doesn’t have this issue.
Enthusiast

Re: Year and Month only field

TDAT123,
when you work with ODBC (sql Assistant) you must do an implicit cast of the results. For example, to get the desired format date you can try this sql via sql assistant:

SELECT current_date (FORMAT 'YYYY/MM') (varchar(80))

The implicit cast is "(varchar(80))". This implicit cast works with all format date and time.
Enthusiast

Re: Year and Month only field

Thanks a lot Pietro ...It really helps a lot.