JULIAN DATE

No Doubt Julian Date conversion is a PAIN in RS. After reading many postings
I came up with the following on the fly conversion for a julian date in our
AS/400 system:
In order to convert the following date 107306 to the regular date: 11/3/2007
I used the following:
First of, the first 2 digits denote the Century: 00 stands for 1900 and 10
stands for 2000
So in the case of 107306 the first 3 digits stand for 2007 and the last 3
stand for number of days.
Here's the formula:
=DATEADD("d", Val(Right(Fields!TransactionDate.Value,3)), "#01/01/200" & Cstr
(Mid(Fields!TransactionDate.Value,3,1)) & "#")
I just wish that when Microsoft marketed RS they had their act together as
far as usability is cocerned, you almost have to be a rocket scientist to
make the reports half way decent! and DON'T get me started on the
ridiculously limited charting functionality!!! Being a recovering Crystal-
holic it has been an absolute BEAR to mimic functionality and looks...
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200712/1On Dec 12, 12:14 pm, "patrickpk via SQLMonster.com" <u38565@.uwe>
wrote:
> No Doubt Julian Date conversion is a PAIN in RS. After reading many postings
> I came up with the following on the fly conversion for a julian date in our
> AS/400 system:
> In order to convert the following date 107306 to the regular date: 11/3/2007
> I used the following:
> First of, the first 2 digits denote the Century: 00 stands for 1900 and 10
> stands for 2000
> So in the case of 107306 the first 3 digits stand for 2007 and the last 3
> stand for number of days.
> Here's the formula:
> =DATEADD("d", Val(Right(Fields!TransactionDate.Value,3)), "#01/01/200" & Cstr
> (Mid(Fields!TransactionDate.Value,3,1)) & "#")
> I just wish that when Microsoft marketed RS they had their act together as
> far as usability is cocerned, you almost have to be a rocket scientist to
> make the reports half way decent! and DON'T get me started on the
> ridiculously limited charting functionality!!! Being a recovering Crystal-
> holic it has been an absolute BEAR to mimic functionality and looks...
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200712/1
I don't know what your date format is, but it isn't a Julian Date --
it's some kind of weird mainframe hybrid of Century, Year and
DayOfYear. A Julian Date is a count of days since Jan 1st, 4713 BC.
(See http://aa.usno.navy.mil/data/docs/JulianDate.php). The Julian
date for 11/3/2007 is 2,454,408.
For you, the following .Net formula works to convert to 11/3/2007, and
it gets your 1900-2000 span right too (replace the number with your
Fields...Value):
= #1/1/1900#.AddYears( Floor( 107306 / 1000) ).AddDays( 107306 Mod
1000 )
If you needed to go backwards for some reason, this works (replace the
date with your Fields...Value) :
= 1000 * ( CDate(#11/3/2007#).Year - 1900 ) +
CDate(#11/3/2007#).DayOfYear - 1
-- Scottsql