Convert JDEdwards "Julian" date (CYYDDD) to SQL datetime

Declare @J integer
Set @J = 105031

select DATEADD(year, @J / 1000, 0) + DATEADD(day, @J % 1000, 0) - 1

You can shorten/simplify that very slightly, like so:


select DATEADD(DAY, @j % 1000 - 1, DATEADD(year, @j / 1000, 0))


Please do *not* give me any points for this, just offering a very mild enhancement to acperkins's code.

>>Please do *not* give me any points for this<<
I beg to differ, I should have thought of that <g>

I seem to have overlooked this:
>>cannot figure how to do it in a SQL Server user defined function (which I believe cannot return a datetime).<<

Create Function dbo.udf_ConvertDate(@Value as integer)
Returns datetime
As
Begin
Return  DATEADD(DAY, @Value % 1000 - 1, DATEADD(year, @Value / 1000, 0))
End

I tried to create this UDF but I get the following error message:

Error 170: Line 1: Incorrect syntax near 'Function'.
Must declare the variable '@Value'.

Am I overlooking something?

I did have success with the SELECT version, and might have to use it with one small change:

SELECT DATEADD(year, cast(JulianValue as int) / 1000, 0) + DATEADD(day, cast(JulianValue as int) % 1000, 0) - 1 as GregorianDate FROM <table>

btw was I wrong about UDFs not being able to return a datetime value?

Thanks very much for all your help!

Convert USA Date to JDE
ALTER FUNCTION dbo.fnConvertToJDEDate
(
      @theDate DATETIME
)

RETURNS INT

AS

BEGIN

DECLARE @C CHAR(1)
DECLARE @YY CHAR(2)
DECLARE @DDD VARCHAR(3)
DECLARE @JDEDate INT

DECLARE @YR INT
SET @YR = YEAR(@theDate)

--Do Century
IF @YR <=2000
      SET @C = '0'
ELSE
      SET @C = '1'

--Do Year
SET @YY = RIGHT(CAST(@YR AS CHAR(4)),2)

--Do Day of Year
SET @DDD = CAST(DATEPART(dy,@theDate) AS VARCHAR(3))

IF LEN(@DDD)=2
      SET @DDD = '0' + @DDD
ELSE IF LEN(@DDD)=1
      SET @DDD = '00' + @DDD


SET @JDEDate = CAST(@C + @YY + @DDD AS INT)

RETURN @JDEDate

tcurtin,

Psst:  This thread was closed over 5 years ago when the solution here http:#a13283060 was accepted.