All Forums Database
TDAT123 5 posts Joined 05/06
01 May 2006
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

01 May 2006

select substr(current_date, 1, 7) year_mo 2006-05select substr(current_date (format 'yyyy/mm'), 1, 7) year_mo2006/05This will be a string.will this work?

TDAT123 5 posts Joined 05/06
01 May 2006

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.

01 May 2006

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 !

Sunil Agrawal 12 posts Joined 07/04
01 May 2006

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.

TDAT123 5 posts Joined 05/06
02 May 2006

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.

Sunil Agrawal 12 posts Joined 07/04
02 May 2006

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.

TDAT123 5 posts Joined 05/06
02 May 2006

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.

pietroNardella 5 posts Joined 05/06
03 May 2006

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.

TDAT123 5 posts Joined 05/06
03 May 2006

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

YzarkLeon 1 post Joined 07/14
10 Jul 2014

Hi I am new to teradata, pls help
I have to_mth field which is integer which stores the mth in yyyymm. I want to limit my select to where to_mth >= current date's YYYYMM. can you give a where clause for this?
thanks! appreciate much

dnoeth 4628 posts Joined 11/04
10 Jul 2014
WHERE to_mth >= EXTRACT(YEAR  FROM CURRENT_DATE) * 100 + 
                EXTRACT(MONTH FROM CURRENT_DATE)

Looks complicated, but the optimizer will resolve it to as a YYYYMM number, check Explain.

Dieter

You must sign in to leave a comment.