Hello Experts
Help Needed Please!
I am trying to convert Julian Date in to Regular Date format. An example of the invoice number is : 1110033123279 Where the numbers in Bold is the julian date.
I need to find a solution in Web Intelligence reporting, please.
I read a few topics here about the conversion, but could not get what I needed. 
Your help is appreciated .Thanks
Rehman BI (BOB member since 2013-04-25)
Which part are you having trouble with, the numeric conversion or calculation or just extracting the relevant numbers from the string?
Thomas Evans 
(BOB member since 2013-10-17)
Thx for the reply.
Its actually extracting the numbers from the string and then converting them to the regular date format.
So far, I have used substring to extract the numbers and changed in to number format, but I was not able to do this all in one variable with converting into regular date format.
Hope I was clear enough. 
Rehman BI (BOB member since 2013-04-25)
I assume you have something like this already?
=FormatNumber(ToNumber(Substr(“1110033123279”;3;5));"#####")
As for the math side of it, I have’t encountered this date format before but if it’s anything like Epoch it should be fairly easy. Do you know the math? I can probably write the variable if I know how it works.
EDIT; Actually the math doesn’t seem too complex, I managed to do it (I love a challenge) Although it seems quite difficult to get accurate values due to leap years (using the calculation I found at least) I suspect there are many calculation variations though…
I suspect that the string you are wanting to evaluate is too short, for instance the Julian Date for today is: 2456948.5, I’m not sure what part of your string to use though, 3x would be in the future and 03 would be a long time ago…
Here’s the variables I built, based on your number and this link (http://quasar.as.utexas.edu/BillInfo/JulianDatesG.html) though:
Q:
=FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####")+0.5Z:
=Round(ToNumber([Q]);0)W:
=Round(([Z]-1867216.25)/36524.25;0)X:
=[W]/4A:
=[Z]+1+[W]-[X]B:
=[A]+1524C:
=Round(([B]-122.1)/365.25;0)D:
=Round(365.25*[C];0)E:
=Round(([B]-[D])/30.6001;0)F:
=Round(30.6001*[E];0)Day of Month:
=Round([B]-[D]-[F]+(ToNumber([Q])-[Z]);0)Month:
=[E]-1Year
=[C]-4715 Worth noting though, there’s some additonal logic that needs to we worked into some of those statements which I skipped over (as I knew your number was too short for anything)
So anyway, the output of all of that is 5th, August 4437 BC 
Cheers,
Thomas
Thomas Evans (BOB member since 2013-10-17)
Thanks for the prompt reply.
I have gotten to this point: =FormatNumber(ToNumber(Substr(“1110033123279”;3;5));"#####")
I was looking at the conversion, but could not find any calculation that converts julian to regular date. To my surprise, I found the conversion from regular date to julian as following:
=FormatNumber(Year([DateObject]);"####")+LeftPad(1+DaysBetween(ToDate(FormatNumber(Year([DateObject]);"####");“yyyy”);[DateObject])+"";3;“0”)
I still do not see the logic behind this… Any help?please
Edit: 10033 will be the 5 digit Julian date, which is June 21st .
Rehman BI (BOB member since 2013-04-25)
Something else I just read about the five digit is that the first 2 numbers should be the year and the later 3 be the day number of the year. You could easily write an if statement the compute that, but once again your example doesn’t line up as expected…
Thomas Evans (BOB member since 2013-10-17)
Thanks for your efforts 
My example has the first 2 numbers as YY and the 3 as DDD. ‘10033’(YYDDD). Isn’t this the right example? Can you please elaborate on the ‘‘if’’ statements that I can use?
Rehman BI (BOB member since 2013-04-25)
Okay. How far back does your data go, is it pre 2000?
Below is something I threw together that will do, obviously you need to extend it:
=If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))> 0 And ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))<31; ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");4;5));
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=32;1;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=33;2;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=34;3;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=35;4;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=36;5;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=37;6;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=38;7;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=39;8;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=40;9;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=41;10;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=42;11;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=43;13;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=44;14;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=45;15;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=46;16;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=47;17;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=48;18;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=49;19;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=50;20;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=51;21;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=52;22;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=53;23;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=54;24;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=55;25;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=56;26;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=57;27;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=58;28;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=59;1;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=60;2;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=61;3;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=62;4;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=63;5;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=64;6;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=65;7;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=66;8;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=67;9;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=68;10;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=69;11;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=70;13;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=71;14;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=72;15;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=73;16;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=74;17;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=75;18;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=76;19;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=77;20;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=78;21;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=79;22;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=80;23;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=81;24;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=82;25;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=83;26;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=84;27;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=85;28;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=86;29;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=87;30;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=88;31;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=89;1;
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))=90;2
))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
+" "+
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))> 0 And ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))<31; "Jan";
If(ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))>= 32 And ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");3;5))<59; "Feb"
))
+", "+20+""+ToNumber(Substr(FormatNumber(ToNumber(Substr("1110033123279";3;5));"#####");1;2)) So effectively in the first section I am decoding the day number to reset it back to the relevant date, if the day falls within the first 31 (January) then we just use that date, otherwise the decode needs to happen - I did this to about March as an example. Secondly I concatenate the Month using a similar sort of range operation, so if its between the 1st and 31st day of the year its January, if it’s from the 32nd to the 59th then it’s February and so on. Then finally I just concatenate 20 to the year string, assuming you have no pre 2000 data.
It would be a whopper of a statement, but will effectively do the job, you’d probably need to factor some additonal smarts in around leap years, which wouldn’t be hard if you just evaluate the year string first then run a day decode operation suitable for that type of year.
Getting all the dates back in a numeric string ie, 20140101 YYYYMMDD would be better then you can just do a date function over it.
Hope this helps.
Thomas
Thomas Evans (BOB member since 2013-10-17)
Okay, let me see if I understand correctly.
If you have a julian date of 10033, that means it is the 33rd day of 2010. Correct?
Wouldn’t it be a lot easier to just add 33 days to 1/1/2010? Something like:
RelativeDate(ToDate("01/01/20" + Substr("1110033123279";3;2);"dd/MM/yyyy));ToNumber(Substr("1110033123279";5;3)))
I mean, not only is that a smaller function, but it already accounts for leap years and whatnot.
Lugh (BOB member since 2009-07-16)
Hi, Please try the below formula for converting Juliandate to calendar date.
dateadd(dd,(Juliandatevalue%1000)-1,dateadd(yy,Juliandatevalue/1000,0))
sukrav 
(BOB member since 2008-01-29)
That is SQL server syntax.
To be honest this is best done by the database and populated into a calendar table.
Mak 1 
(BOB member since 2005-01-06)
Thank you all for the reply.
Thomas: I am not sure if this will be acceptable by the business user, I mean I have converted to regular date in the universe, with a simple ‘mod’ function in teradata, so there could be a function like this supported in Webi.
Lugh: 10333 would be April 16, as per http://aa.usno.navy.mil/data/docs/JulianDate.php
All: Is there a webi formula for the conversion, or will I have to just deal with it in the universe?? Also, the db I am using is Teradata.
Please
Rehman BI (BOB member since 2013-04-25)
I have no experience en Teradata, so I don’t know if what I’m saying is possible:
Create a measure variable (let’s call it Julian Date) with the extracted string transformed to number.
Create a date variable (let’s call it Year 0) with the value 01/01/-4713, which is the year 0 on julian date.
Use the function
RelativeDate([Year 0]; [Julian Date])to calculate the regular date.
In the example, I just don’t understand why the julian date have only 5 digits. The gregorian date for this string is April 16, 4685 BC. But, anyway, you should know the information you’re work on better than me. 
joaofpo 
(BOB member since 2007-03-29)