Create SQL Date from JDE Numeric Date Value?

[JoeSalvatore]

Does anyone have an example where, using TSQL, they have taken a JDE numeric date value (like the DateForGLandVoucherJULIA) and have changed it to a regular date (MM/DD/YYYY)?

These numeric date values are stored to represent a date as CYYDDD.
Thus 103022 would really be 01/22/2003.

Has anyone done this conversion in SQL views using TSQL or TSQL functions?

 

[JGair]

The following code will do what you are asking (where DGL represents your Julian Date field):

SELECT DATEADD(day,(DGL%1000)-1,DATEADD(year,DGL/1000,'01/01/1900'))

You can also create a User Defined Function on SQL Server using this code and then you can just call the UDF whenever you need to.

Jeff Gair
Systems Analyst/Developer
Royal Group Technologies Ltd.

 

[JoeSalvatore]

The dateadd solution provided errors with "Your entry cannot be converted to a valid datetime value" message in SQL Enterprise Manager.

Let me clarify:
The DGL field in JDE is a 9 digit number field (not a date)
The numbers in the dB representation are as follows:
1st digit is a century indicator (0 = 19 and 1 = 20)
2nd & 3rd digits represent the calendar year
4th, 5th & 6th digits represent the day of the year

I need this converted into a standard SQL datetime (e.g. 01/01/1900)

Example:
JDE F0911 GLDGJ = 103168 should be converted to 06/17/2003

ANY ASSISTANCE GETTING THIS TO WORK WOULD BE MOST WELCOME AND APPRECIATED!

 

[bwilkinson]

Try this

Example of new way to do SQL Select.

SELECT SDLITM, SDDSC1, SDNXTR, SDLTTR, SDUORG, SDSOQS, SDLNID, SDADDJ,
convert(varchar,cast('1/1/'+cast(left(right(SDTRDJ+1000000,6),3)+1900 as
varchar) as datetime)+ (right(SDTRDJ,3)-1),101) as SDTRDJGregorianDateMMDDYY
FROM PRODDTA.F4211

This will still select 9 fields but the 9th field (now called
SDTRDJGregorianDateMMDDYY instead of SDTRDJ) is now in mmddyy format instead
of the 101154 FORMAT


-Brian

Brian Wilkinson
Quickie Manufacturing





William “Brian” Wilkinson
Quickie Manufacturing
[email protected]
856-829-7900 x143

 

[eydeak]

I haven't done this in SQL Server, but the following is a formula I use in
excel spreadsheets to convert JDE dates to Microsoft dates. (M104 is the
cell containing the JDE date). This works for dates before and after the
year 2000.

DATE((1900+LEFT(TEXT(M104,"000000"),(LEN(TEXT(M104,"000000"))-3))),1,1) +
(RIGHT(TEXT(M104,"000000"),3)-1)

You may need to do some tweaking to turn this into a stored procedure for
SQL, but it ought to get you going.

Ellen Deak
Senior Analyst/Programmer
Cooper Standard Automotive
OneWorld Xe, AS/400 DB2



Ellen Deak
Senior Analyst/Programmer
Cooper-Standard Automotive
OneWorld Xe (B7333, Service Pack 17)
AS/400 DB2

 

[JMR]

Joe,

Not sure about your error...does it ever work?

Try, in your select statement, CASTing your dgl value to int:

DATEADD(day, CAST(xxDGL as int)%1000-1, DATEADD(year, CAST(xxDGL as int)/1000, '01/01/1900'))

Good Luck.

 

[JGair]

Sorry it did not work for you Joe, I'm not sure what would cause that error, I have not been able to reproduce it here.

Here is how this code works:

The second DATEADD statement takes the numeric date, for example 103168, and divides it by 1000 to get the number of years to add to the base date of Jan. 1 1900, in this case it would be 103 added to 1900 to get the date 01/01/2003.

The first DATEADD statement takes the remainder of the numeric date divided by 1000 and subtracts 1 to get the number of days to add to the date returned above, so it would be 167 days added to 01/01/2003 and the result would be 06/17/2003.

A few things to try:
1. Try putting a constant in the expression to test it, for example

SELECT DATEADD(day,(103168%1000)-1,DATEADD(year,103168/1000,'01/01/1900'))

If that works then you can work from there to figure out why the columns you are using are giving you an error.

2. Have you tried Jeremy's suggestion of CASTing the Julian date fields to integer first? This statement requires integers to do the math properly.

3. You might try specifiying a different date format like '19000101'.

I don't know what else to suggest.


Jeff Gair
Sysems Analyst/Developer
Royal Group Technologies Ltd.

 

[AlexLD]

Re: RE: Create SQL Date from JDE Numeric Date Value?

Do you have the same type of formula to go the other way around in Excel? Thanks!

 

[peterbruce]

Re: RE: Create SQL Date from JDE Numeric Date Value?

AlexLD,

Welcome to JDEList.

Try this, where A5 is the microsoft date:

(YEAR(A5)-1900)*1000+(A5-DATE(YEAR(A5),1,1)+1)