Julian date to standard date for multiple columns using macros

Question

Hello Folks,

I am beginner in Alteryx,,

I am trying to create WF using Alteryx . In there i am fetching the data from Oracle database.

Tables having several fileds of date which are in Julian date format.

For the conversion of date from julian to standard date i am using macros named "JulianDateToStandardDateConverter" . But the problem is that this is only i can used for single field/column from the table .

The table having more than 1 field/column contains julian date, so how we can use it the JulianDateToStandardDateConverter macro in the WF ?

or There are any other method or Macros which allowed multiple columns for julian date conversion?

Please help

Indrajeet_TN · Answer

sharing the screenshots of WF

Please check

Julian Date Conversion.docx

Indrajeet_TN · Answer

Hello Messi007,

Tried this below syntax,

to_char(to_date(to_char("PKUPMJ"+1900000),'YYYYDDD'),'YYYY-MM-DD') --its worked good for  PKUPMJ field.

to_char(to_date(to_char("PKCRTJ"+1900000),'YYYYDDD'),'YYYY-MM-DD') -- its not wokring  this PKCRTJ field.

Its giving an error

Info: Write Data In-DB (9): ODBC Driver version: 03.52
Warning: Write Data In-DB (9): Executing PreSQL: "DROP TABLE "BIDEV"."F56C0092";" (error ignored) : [Oracle][ODBC][Ora]ORA-00942: table or view does not exist
Error: Write Data In-DB (9): Executing PreSQL: "CREATE TABLE "BIDEV"."F56C0092" AS WITH "Tool2_ca2a" AS (select *
from SY910.F56C0092@JDEDB), "Tool17_32ac" AS (SELECT "UCUS..." : [Oracle][ODBC][Ora]ORA-01848: day of year must be between 1 and 365 (366 for leap year)
Error: Write Data In-DB (9): Error running PreSQL on "NoTable": [Oracle][ODBC][Ora]ORA-01848: day of year must be between 1 and 365 (366 for leap year)

Can you please check this

danilang · Answer

Hi @Indrajeet_TN

There are multiple formats that are collective known as Julian dates.  One of the more common ones is 202182, which is the 82nd day of 2021. or Mar 23, 2021.

No matter what your format is, you can use a Multi Field Formula tool to apply the transformation to multiple fields at the same time.  The conversion formula is DateTimeParse(ToString([_CurrentField_]),"%Y%j")

Dan

FromJulianDate.yxmd