How do I use DayNumberofYear function to always return a 3 digit day

[JayG]

I am using DayNumberofYear(Orderdate)  function to produce a Julian date based on the Orderdate field, with the year starting on Jan 1st. I need the function to return a 4 digit year followed by a 3 digit day (representing the # of days since January 1st). In cases where the day is less that 100 (which is April 10), the day displays as one or two digit numbers. I need it to always be a three digit numbers. In these cases, I need one or two leading zeros before the day, for example Jan 01, 2025 needs to display as "2025001" [but function returns "20251"], Apr 01, 2025 needs to display as "2025091" [but returns "202591"].  Apr 10, 2025 displays correctly because day 100 is three digits already so displays as "2025100".  Anyone have any idea how I can do this?

Here is the script I am currently using in the Data Load Editor: 

Year(Orderdate) & DayNumberOfYear(Orderdate) as Orderdate_Julian

[Clement15 · Accepted solution]

Hello,

This should meet your needs.

Year(Orderdate) & Right('000' & DayNumberOfYear(Orderdate), 3) as Orderdate_Julian

[paulwilli]

HI

try this:

Year(Orderdate) & Repeat(0,3- len(DayNumberOfYear(Orderdate))) & DayNumberOfYear(Orderdate)

[rwunderlich]

Year(Orderdate) & Num(DayNumberOfYear(Orderdate), '000') as Orderdate_Julian

-Rob

[JayG]

Worked like a charm! Thanks!!