mysqldatetimeteradata-sql-assistant

How to Get previous month and current year in Teradata/SQL

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.

Solution

  • 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))