[CLOSED] Getting multiple dates from CURRENT_DATE() in Data Migrator
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, AnishaThis message has been edited. Last edited by: FP Mod Chuck,
Data Migrator 8206
May 04, 2020, 11:15 AM
AJillu
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,
Data Migrator 8206
May 07, 2020, 10:20 AM
FP Mod Chuck
Ajillu
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
May 08, 2020, 11:41 AM
Clif
IIUC you want the first and last days of the previous month. You can do that with: