convert Julian Date to Calendar Date

How do I convert Julian Date to Calendar Date in T-SQL / DTS based on
following guideline found at Internet?
To convert Julian date to Gregorian date:
double JD = 2299160.5;
double Z = Math.Floor(JD+0.5);
double W = Math.Floor((Z - 1867216.25)/36524.25);
double X = Math.Floor(W/4);
double AA = Math.Floor(Z+1+W-X);
double BB = Math.Floor(AA+1524);
double CC = Math.Floor((BB-122.1)/365.25);
double DD = Math.Floor(365.25*CC);
double EE = Math.Floor((BB-DD)/30.6001);
double FF = Math.Floor(30.6001*EE);
double Day = BB-DD-FF;
double Month;
double Year;
if((EE-13) <= 12 && (EE-13) > 0)
Month = EE-13;
else
Month = EE-1;
if(Month == 1 || Month == 2)
Year = CC-4715;
else
Year = CC-4716;Sam
declare @.julian char(8)
select @.julian = '1996.031'
declare @.dt datetime
select @.dt =
DATEADD(dd,convert(int,right(@.julian,3))
-1,convert(datetime,substring(@.juli
an,1,4)+'0101',212))
select @.dt
"Sam" <cybersam88@.hotmail.com> wrote in message
news:uTQSoztSFHA.2336@.TK2MSFTNGP12.phx.gbl...
> How do I convert Julian Date to Calendar Date in T-SQL / DTS based on
> following guideline found at Internet?
> To convert Julian date to Gregorian date:
> double JD = 2299160.5;
> double Z = Math.Floor(JD+0.5);
> double W = Math.Floor((Z - 1867216.25)/36524.25);
> double X = Math.Floor(W/4);
> double AA = Math.Floor(Z+1+W-X);
> double BB = Math.Floor(AA+1524);
> double CC = Math.Floor((BB-122.1)/365.25);
> double DD = Math.Floor(365.25*CC);
> double EE = Math.Floor((BB-DD)/30.6001);
> double FF = Math.Floor(30.6001*EE);
> double Day = BB-DD-FF;
> double Month;
> double Year;
> if((EE-13) <= 12 && (EE-13) > 0)
> Month = EE-13;
> else
> Month = EE-1;
> if(Month == 1 || Month == 2)
> Year = CC-4715;
> else
> Year = CC-4716;
>|||Well, if you really want to use that formula, then just assign a bunch of
values, along the lines of:
declare @.JD float,
@.Z float,
@.W float,
@.X float,
@.AA float,
@.BB float,
@.CC float,
@.DD float,
@.EE float,
@.FF float
set @.JD = 2299160.5
set @.Z = @.JD+0.5
set @.W = (@.Z - 1867216.25)/36524.25
set @.X = @.W/4.0
set @.AA = @.Z+1+@.W-@.X
set @.BB = @.AA+1524
set @.CC = (@.BB-122.1)/365.25
set @.DD = 365.25*@.CC
set @.EE = (@.BB-@.DD)/30.6001
set @.FF = 30.6001*@.EE
declare @.day float, @.month float, @.year float
set @.Day = @.BB-@.DD-@.FF
if ((@.EE-13) <= 12) and ((@.EE-13) > 0)
set @.Month = @.EE-13
else
set @.Month = @.EE-1
if(@.Month = 1 OR @.Month = 2)
set @.Year = @.CC-4715
else
set @.Year = @.CC-4716
select @.day, @.month, @.year
But I'm not sure that's right. I think a Julian date is just the number of
days since some date about 4000 BC... and that you do it like this:
declare @.mydate datetime
set @.mydate = dateadd(dd, @.JD-2451545, '1-jan-2000')
But this is assuming you just have an integer for your number. If it's a
float, then you will also want to do something like:
select dateadd(second,86400 * (@.JD - convert(bigint,@.JD)),@.mydate)
Which just adds on the fraction to the number of seconds.
Hope this helps,
Rob|||How about convert VB Script to T-SQL?
CalendarDate = Date(1900+INT(JulianDate/1000),1,MOD(JulianDate,1000))
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O8TdBbuSFHA.3980@.TK2MSFTNGP12.phx.gbl...
> Sam
> declare @.julian char(8)
> select @.julian = '1996.031'
> declare @.dt datetime
> select @.dt =
> DATEADD(dd,convert(int,right(@.julian,3))
-1,convert(datetime,substring(@.ju
li
> an,1,4)+'0101',212))
> select @.dt
>
>
> "Sam" <cybersam88@.hotmail.com> wrote in message
> news:uTQSoztSFHA.2336@.TK2MSFTNGP12.phx.gbl...
>|||Sam
declare @.julian char(8)
select @.julian = REPLACE('1996.031','.','')
SELECT CAST(CAST(@.julian /1000 AS CHAR(4))+'01'+CAST(@.julian %1000 AS
CHAR(2))AS DATETIME)
"Sam" <cybersam88@.hotmail.com> wrote in message
news:elKd5AwSFHA.3244@.TK2MSFTNGP15.phx.gbl...
> How about convert VB Script to T-SQL?
> CalendarDate = Date(1900+INT(JulianDate/1000),1,MOD(JulianDate,1000))
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O8TdBbuSFHA.3980@.TK2MSFTNGP12.phx.gbl...
DATEADD(dd,convert(int,right(@.julian,3))
- 1,convert(datetime,substring(@.juli[color
=darkred]
>