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:
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
Please help me with the code.
Thanks a lot,
AnishaThis message has been edited. Last edited by: FP Mod Chuck,
Data Migrator 8206
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.
Anisha.This message has been edited. Last edited by: AJillu,
Data Migrator 8206
Since no one has been able to provide you any help I suggest you open a case with techsupport.
Thank you for using Focal Point!
Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
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)
|Powered by Social Strata|