calendar date to 5 digit julian date

[twes3]

Hello,

I need to convert a calendar date parameter (example 11/12/2012) to a 5 digit julian date (example 12317). The 5 digit julian is how I will access my data. Any ideas to how to do this?

Thank you in advance.

 

[IanWaterman]

This is based on an Excel formula, converted to crystal syntax

(YEAR(currentdate)-2000+100)*1000+Currentdate-DATE(YEAR(currentdate),1,1)+1

This returns 112317, so not sure how you get a 5 digit Julian date

Ian

 

[fisheromacse]

maybe this:

\\{@JDate}
stringvar yr := RIGHT(totext(year({YourTable.YourDate}),0),2);
stringvar dy := totext(DateDiff('d',Date(year({YourTable.YourDate}),1,1),currentdate),0);
yr&dy


it will return the last 2 digits of the year and the number of days between the 1st of January and today's date.

hopefully i don't have any syntax or logic errors, if i do, my apologies in advance.

 

[IanWaterman]

After some research on 5 digit Julian

((Year(currentdate)-2000)*1000)+datediff('d', Date(Year(currentdate), 1, 1), currentdate)+1

Ian

 

[twes3]

Thanks everybody for the advice. I forgot to mention my 5 digit julian date is numeric. So i will need a conversion for numeric.

Thanks again.

 

[fisheromacse]

If you add the line below to the formula i provided then it will return a number.

IF isnumeric(yr) and isnumeric(dy) then tonumber(yr&dy) else 0


I was thinking a bit more about it and if the value entered by the parameter is not within the current year, there could be problems with the formula i provided. i have not tested this, but it is a thought to consider.
For that reason, Ian's may be a better solution, and his also returns a number without any sort of additional effort.

 

[twes3]

Thank you all for the advice. I used Ian suggestion and it works like a charm. Thank again.

((Year(currentdate)-2000)*1000)+datediff('d', Date(Year(currentdate), 1, 1), currentdate)+1