January 4, 2008 at 2:19 am
Hi,
I have been involved with a system that uses Julian date instead of
Gregorian.
The dates are stored as decimal.
Thus '2008-01-04 00:00:00.000' is stored as 108004.
We use a function that translates from decimal to datetime.
This one:
CREATE FUNCTION dbo.udfConvertJulianDate
(@nJulianDate NUMERIC(9))
RETURNS DATETIME
AS
BEGIN
DECLARE @vcJulianDate AS VARCHAR(6)
DECLARE @dteDate DATETIME
SET @dteDate = '1900-01-01'
IF isnull(@nJulianDate,0) > 0
BEGIN
SET @vcJulianDate = CONVERT(VARCHAR(6),@nJulianDate)
SET @vcJulianDate = REPLICATE('0',6 - LEN(@vcJulianDate)) +
@vcJulianDate
SET @dteDate =
DATEADD(yyyy,CONVERT(INT,SUBSTRING(@vcJulianDate,1,3)),
@dteDate)
SET @dteDate =
DATEADD(dd,CONVERT(INT,SUBSTRING(@vcJulianDate,4,3)) - 1,
@dteDate)
END
RETURN @dteDate
END
The problem:
select dbo.udfConvertJulianDate(108004) as 'Today'
select dbo.udfConvertJulianDate(108000) as 'Today - 4'
select dbo.udfConvertJulianDate(107999) as 'Today - 5'
select dbo.udfConvertJulianDate(108004-640) as 'Today - 640'
Today
------------------------------------------------------
2008-01-04 00:00:00.000
Today - 4
------------------------------------------------------
2007-12-31 00:00:00.000
Today - 5
------------------------------------------------------
2009-09-25 00:00:00.000
Today - 640
------------------------------------------------------
2007-12-30 00:00:00.000
/m
January 4, 2008 at 10:13 am
looks like you have some holes in your logic...let me try to explain.
typically, a julian date is the number of dasy since a certain start date.
select getdate(),convert(int,getdate())
results:
DATE: 2008-01-04 12:00:38.343
As INT: 39450
so that's 39450 days since day zero in sql server, which is 01/01/1900
techinically, the real julian calendar typically starts at January 1, 4713 BC, so the same method woud be 4713 BC years plus 1900 years bigger, times 365, which is quite a big number: 2454469 Julian Days, which is not as developer friendly as SQL's beginning date system.
It looks like you are using a custom version of Julian logic, so 108004 would be 1900 + year offset of 108 + number of days
your logic would be expecting that the portion under 1000 would always be less than 365.. ie 108365 should be Dec 31 2008
so 107999 is NOT the same same as 108004 -5! 107365 would be 5 days before under the logic implemented.
107999 is 01/01/2007 + 999 days! (almost 3 years.)
I would streamline your procedure to this, but you might want to rething your date storage.... 39450 days is easy to convert via SQL, instead of a custom date like 108004
CREATE FUNCTION dbo.udfConvertJulianDate2
(@nJulianDate NUMERIC(9))
RETURNS DATETIME
AS
BEGIN
DECLARE @YearOffset int
DECLARE @JulianDate int
SET @YearOffset = CONVERT(int,@nJulianDate / 1000) --108 for 2008, right? 1900 + 108
SET @JulianDate = CONVERT(int,CONVERT(int,@nJulianDate) % 1000) --modulus, ie 241 (< 365/6 leap year
RETURN DATEADD(dd,@JulianDate,DATEADD(yyyy,@YearOffset,CONVERT(datetime,'1900-01-01')) )
END
select dbo.udfConvertJulianDate2(108004) as 'Today'
select dbo.udfConvertJulianDate2(108000) as 'Today - 4'
select dbo.udfConvertJulianDate2(107999) as 'Today - 5'
select dbo.udfConvertJulianDate2(108004-640) as 'Today - 640'
--same results, as the "day portion" of your sample dates are sqewed due to the 365 limit.
2008-01-05 00:00:00.000
2008-01-01 00:00:00.000
2009-09-26 00:00:00.000
2007-12-31 00:00:00.000
Lowell
January 4, 2008 at 8:13 pm
Heh... in other words, there's no such thing as a Julian date of "107999"... can only go up to 107365... next day would be 108001.
With that in mind, the following works just fine and has no character conversions to slow it down...
SELECT DATEADD(yy,@JulianDate/1000,0) + @JulianDate%1000 -1
--Jeff Moden
Change is inevitable... Change for the better is not.
January 8, 2008 at 3:19 am
Many thanks, now I "see the light".
/m
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply