Need to convert a julian date to standard date (gregorian) in a BAQ

ERP 10
1

I have a 5 digit julian date that I would like to convert to a gregorian date format and then add 90 days to it.

For example, today’s julian date is 17256, which equals September 13th, 2017, add 90 days and it would be December 12th, 2017.

I would like to add a calculated field in my BAQ that would convert julian format to gregorian format.

Is this possible?

Thanks,
Joe

2

I think you can do this with a few TSQL commands (which will work in your BAQ).

First, get the number of days from your date into an integer. If not already, convert your Julian to a string then:

CAST(RIGHT(‘17256’,3) AS int) – 1

this should return 255. Now you add this number to the beginning of the year:

CONVERT(sqlDateVar,‘01/01/’ + LEFT(‘17256’,2), 1) // Use 101 if you include the century in your string

So now sqlDateVar holds the date for the first day of the year. All you have to do is add the number of days from above:

DATEADD(day, 255, sqlDateVar)

To add your 90 days, just add that to the number of days above. You can create intermediate variables or combine it into one calculated field once you’re comfortable.

Mark W.

2 Likes
3

Thank you, Mark. I appreciate your time and will advise if I get it to work.

4

Mark,
I am getting a syntax error when attempting the first function. I have added screen shots below to help with our discussion and to confirm that I have the base BAQ created correctly. Currently my work around is to dump this report into an Excel template. Please review at your leisure.

Thanks,
Joe

Here is the BAQ report:

image
image.jpg839×104 57.6 KB

Here are the three calculated fields:
image
image
image