Julian date to standard date for multiple columns using macros

[Indrajeet_TN]

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

[messi007]

@Indrajeet_TN,

You can use the formulas below:

1- Parse the column

DateTimeParse([YourField],"%Y%j")

2- You can change the format by adding on:

 DateTimeFormat(DateTimeParse([ParsedField],"%Y%j"), "%m/%d/%Y")

Regards,

[Indrajeet_TN]

Hello Messi007,

I there anything we can use exesting macros or rebuild macros using multiple input parameters for julian date 

[messi007]

@Indrajeet_TN,

Could you share the macro you're using please.

It will help me to make it dynamic.

Regards,

[danilang]

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 

[Indrajeet_TN]

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 

[Indrajeet_TN]

sharing the screenshots of WF 

Please check