T-SQL Julian Date Conversion Code

Phil Auer

Member
I am generating interface files from a SQL Server JDE database using T-SQL and am looking for code to convert the Julian date to a readable character string. I have trolled through the download section but have so far found no appropriate solution.

Does anyone know of something? I don't want to re-invent the wheel.
 
Use this for the time being:
CREATE FUNCTION dbo.cj (@JulianDate int)
RETURNS Datetime
AS
BEGIN
/* THIS USER DEFINED FUNCTION TAKES A PEOPLESOFT DEFINED JULIAN DATE VALUE IN THE FORMAT
CENTURY/YEAR/MONTH/DAYS FROM JAN.1
THIS USER DEFINED FUNCTION SPLITS THE JULIAN DATE INTO COMPONENT PARTS AND RETURN THE
CORRESPONDING DATE/TIME VALUE */
DECLARE @str as varchar(10)
SET @str = CAST(@JulianDate as varchar(10))
/* FIRST PARSE OFF THE CENTURY PORTION OF THE JULIAN DATE, THIS ONLY APPLIES IF THE
PASSED VALUE IS GREATER THAN 100000. ONCE WE UNDERSTAND THE CENTURY THAT WE
ARE DEALING WITH THEN DETERMINE THE YEAR. */
DECLARE @Century as varchar(2)
DECLARE @Year as varchar(4)
IF @JulianDate = 0
RETURN 0
IF @JulianDate >=100000
BEGIN
SET @Century = LEFT(@str,1)
SET @str = RIGHT(@str, LEN(@str)-1)
IF @Century = '1'
BEGIN
SET @Year = '20' + LEFT(@str,2)
END
IF @Century = '2'
BEGIN
SET @Year = '21' + LEFT(@str,2)
END
END
ELSE
BEGIN
SET @Year = '19' +LEFT(@str,2)
END
SET @str = RIGHT(@str, LEN(@str)-2)
-- NOW EXTRACT THE DAYS SINCE JAN. 1 OF THE SPECIFIED YEAR
DECLARE @Days as varchar(3)
Set @Days = @str-1
-- ASSEMBLE THE STARTING DATE FOR THE YEAR
Set @str = '01/01/' + @Year
DECLARE @Date as datetime
SET @Date = CONVERT(datetime, @str, 101)
-- RETURN THE DATE/TIME WITH THE DAYS ADDED TO JAN.1
RETURN DateAdd(Day,CAST(@Days as int), @Date)
END
 
Try searching the whole site - not just the download section.
The answer is out there.
 
Here is more t-sql code to look through. This shows multiple parsing actions against a julian date.

<font class="small">Code:</font><hr /><pre>
declare @jul decimal
declare @jul1 decimal
set @jul1 = 104092
declare @jul2 decimal
set @jul2 = 135365
declare @myjul decimal
declare @refDate datetime

set @myjul = ((100 + (datepart(yy,GetDate())-2000))*1000) + (datepart(dy,GetDate()))
--set @refDate = dateadd(day,(-1)* (datepart(dy,GETDATE())-1),GETDATE())
set @jul=@jul1
declare @tmp numeric
set @tmp = (cast(substring(cast(@jul as nvarchar(6)),2,2) as decimal) + 2000)
print 'tmp: ' + cast(@tmp as nvarchar(4))
print 'tmp_1: ' + cast((cast(substring(cast(@jul as nvarchar(6)),2,2) as decimal) + 2000) as nvarchar(4))
--set @refDate = cast((cast(substring(cast(@jul as nvarchar(6)),2,2) as decimal) + 2000)+ '-1-1 12:00:00.00' as datetime)
set @refDate = cast(cast((cast(substring(cast(@jul as nvarchar(6)),2,2) as decimal) + 2000) as nvarchar(4)) + '-1-1 12:00:00.00' as datetime)
print cast(@refdate as nvarchar(30))
/*
print cast(@jul as nvarchar(6)) + ', ' +
cast(@jul as nvarchar(6)) + ', ' +
cast(@myjul as nvarchar(6)) + ', ' +
cast(@refDate as nvarchar(30))

print 'days: ' + cast((-1*(@myjul-@jul)) as nvarchar(5))
print 'Test 1 (previous jul date - ' + cast(@jul as nvarchar(6)) + '): ' +
cast(month(dateadd(day,(-1*(@myjul-@jul)),@refdate)) as nvarchar(2)) + '/' +
cast(day(dateadd(day,(-1*(@myjul-@jul)),@refdate)) as nvarchar(2)) + '/' +
cast(year(dateadd(day,(-1*(@myjul-@jul)),@refdate)) as nvarchar(4))
*/
print cast((cast(substring(cast(@jul as nvarchar(6)),3,1) as decimal) + 100)*1000 as nvarchar(6))
--(cast(substring(cast(@jul as nvarchar(6)),3,1) as decimal) + 100)*1000
print 'base: ' + cast(@jul as nvarchar(6))
print 'Month: '
print cast(month(dateadd(day,(@jul-((cast(substring(cast(@jul as nvarchar(6)),2,2) as decimal) + 100)*1000)-1),cast(cast((cast(substring(cast(@jul as nvarchar(6)),2,2) as decimal) + 2000) as nvarchar(4)) + '-1-1 12:00:00.00' as datetime))) as nvarchar(2)) + '/' + cast(day(dateadd(day,(@jul-((cast(substring(cast(@jul as nvarchar(6)),2,2) as decimal) + 100)*1000)-1),cast(cast((cast(substring(cast(@jul as nvarchar(6)),2,2) as decimal) + 2000) as nvarchar(4)) + '-1-1 12:00:00.00' as datetime))) as nvarchar(2)) + '/' + cast(year(dateadd(day,(@jul-((cast(substring(cast(@jul as nvarchar(6)),2,2) as decimal) + 100)*1000)-1),cast(cast((cast(substring(cast(@jul as nvarchar(6)),2,2) as decimal) + 2000) as nvarchar(4)) + '-1-1 12:00:00.00' as datetime))) as nvarchar(4))
print 'Day: '
print cast(day(dateadd(day,(@jul-((cast(substring(cast(@jul as nvarchar(6)),2,2) as decimal) + 100)*1000)-1),cast(cast((cast(substring(cast(@jul as nvarchar(6)),2,2) as decimal) + 2000) as nvarchar(4)) + '-1-1 12:00:00.00' as datetime))) as nvarchar(2)) + '/'
print 'Year: '
print cast(year(dateadd(day,(@jul-((cast(substring(cast(@jul as nvarchar(6)),2,2) as decimal) + 100)*1000)-1),cast(cast((cast(substring(cast(@jul as nvarchar(6)),2,2) as decimal) + 2000) as nvarchar(4)) + '-1-1 12:00:00.00' as datetime))) as nvarchar(4))
</pre><hr />
 
Not sure if this may be common knowledge, but the following simple Microsoft (eg Excel - Access is similar) code will convert Julian into MS date:-

=INT((LEFT(A1,3))*365.259)+(RIGHT(A1,3))
(Where A1 contains the Julian date)

Apologies if everyone already knows this but I think it's a little gem !
 
Back
Top