I need a simple TERADATA/SQL statement which gives previous month and current year in the following format. In this case as '03 2022'.

I have tried to solve a similar request for current date in required format as '04/15/2022' by exec the below command

SELECT CURRENT_DATE(FORMAT 'mm/dd/yyyy') (CHAR(12)) as 'New_format'.

But now I am struggling to get exact value as 'mm yyyy' which gives previous month and current year.

I have tried several CAST and CONCAT, but none seems to be working. Please guide me.

Thank you in advance.

CodePudding user response：

I would leave it as a comment but I dont have enought reputation.

SELECT DATE - INTERVAL '1' MONTH

More about it in link: https://etl-sql.com/teradata-date-functions/

CodePudding user response：

Either

TO_CHAR(ADD_MONTHS(CURRENT_DATE,-1),'mm yyyy')

which returns VARCHAR - could then CAST to CHAR if needed, or

CAST(CAST(ADD_MONTHS(CURRENT_DATE,-1) AS FORMAT'mmbyyyy') as CHAR(7))

or legacy syntax

ADD_MONTHS(CURRENT_DATE,-1)(FORMAT'mmbyyyy')(CHAR(7))