Converting standard date string to JDE date for a where clause in SQL

[kadamsinco]

Does anyone have a routine for MS SQL Server that will convert a standard date string '01/01/2005' to a jde date '105001'?

I have a set of functions that convert a JDE date to a standard date string:

convert(datetime,(dateadd(dy,t1z103trdt - round (t1z103trdt,-3,0),'1/1/' + substring(convert(char(8),1900+(round(t1z103trdt,-3,0)*.001)),1,4))-1),1)

but I need one that does the reverse. I have searched JDEList but can't seem to find a post that has this solution. Any help you can provide would be appreciated.

Thanks,

Keaton

 

[RWayne]

Keaton:

In Oracle, we use:

to_number(to_char(to_date('03/31/2005','MM/DD/YYYY'),'RRRRDDD'))-1900000

Of course, you have to watch out for null dates with a decode. Perhaps that would help you find an answer for MS SQL?

Good luck!

Roby

 

[dschlieder]

I use this function in MS SQL - I'm sure you could modify it to take a string instead of a date if you need to.


CREATE FUNCTION dbo.fn_JDEJulianDate (@FromDate DateTime)
RETURNS INT AS
BEGIN

RETURN ((YEAR(@FromDate) - 1900) * 1000) + DATEDIFF(DAY,CAST('1/1/' + CAST(YEAR(@FromDate) AS CHAR(4)) AS DATETIME),@FromDate) + 1

END


Regards,

Dave Schlieder