Hello Every one, I am having trouble figuring out the equivalent code for below scenario.

I am trying to convert CURRENT_DATE() to 2 digit month but its notworking. I used EXTRACT(MONTH FROM CURRENT_DATE()), and also used MONTH(CURRENT_DATE()) but both the functions giving only single digit month.

If I get the double digit month, i can concatenate YYYY||Q||MM and convert it to number using TO_NUMBER.

Let me know if there is a better way to get the below values from CURRENT_DATE()

I need below values from CURRENT_DATE() in the format of YYYYQMM as integer (EX: 2020204, 2020205,2020206,2020307 ..etc)

1) PREVIOUS_MONTH = Previous month (EX: 2020204)
2) CURRENT_MONTH = Current Month (EX: 2020205)
3) Next_YEAR_END = Next year end (EX: 2021412)
4) CURRENT_YEAR_BEGIN = Current Year Begin (EX: 2020101)
5) CURRENT_YEAR_END = current Year End (EX: 2020412)


My DM Flow generated SQL is below:

SELECT
MAX(FM.CURRENT_MONTH_ID ) AS Current_ID ,
CURRENT_DATE() AS "CURRENT_DATE" ,
EXTRACT(MONTH FROM CURRENT_DATE() ) AS Current_MONTH ,
MONTH(CURRENT_DATE() ) AS Current_Month_2 ,
YEAR(CURRENT_DATE() ) AS CURRENT_YEAR ,
QUARTER(CURRENT_DATE() ) AS Current_Quarter
FROM
factmanufacture FM



Please help me with the code.

Thanks a lot,
Anisha

This message has been edited. Last edited by: FP Mod Chuck,

I Figured out all the data fields except previous month.

I tried using the DATEADD, DATEMOV functions to get the Previous month First Day or Previous Month Last day as I just want the Previous Year, Quarter and month numbers.

DATEMOV(CURRENT_DATE(), 'BOM') -1

When validating its not giving any exceptions/message, but when i click the "Test SQL Statement" getting the error saying below statement

(FOC14155) AN EQUALITY CONDITION IS REQUIRED FOR SUB-QUERY IN SELECT LIST


Getting same error when trying to use the DATEADD function

I am trying to get the the previous month in a Data Flow SQL.

I am able to get the previous month in the Data Transform but not able to get the two digit month. Help me a way to get the two digit month.


Thanks,
Anisha.

This message has been edited. Last edited by: AJillu,

Ajillu

Since no one has been able to provide you any help I suggest you open a case with techsupport.

IIUC you want the first and last days of the previous month. You can do that with:

 DTRUNC(CURRENT_DATE()- 1 MONTH , MONTH) 
DTRUNC(CURRENT_DATE()- 1 MONTH , MONTH_END)