In Focus

MS-SQL To JD Edwards Date Conversion

This blog helps to understand how we can do MS-SQL to JD Edwards date conversion.

Here, I am going to show you how to see data such as MS-SQL.

Actual JD Edwards data

My linked Server name is “TESTDTA” (JDEdwards database). Open new query Window and type the query given below & press F5.

  1. SELECT VJDGJ FROM [TESTDTA].[F1001]   

Output

 

Convert as SQL Data 
  1. SELECT (DATEADD(DAY, VJDGJ % 1000, DATEADD(YEAR, VJDGJ / 1000, -1)))  
  2. FROM [TESTDTA].[F1001]   

Output

 

Meanwhile, we can insert the data to JD Edwards and without convention, it shouldn’t affect the JD Edwards table. Thus, before inserting the data, convert it, using the query given below.

  1. --INSERT INTO [TESTDTA].[F1001] (VJDGJ)  
  2. SELECT (1000 * DATEDIFF(YEAR, 0, GETDATE()) + DATEPART(DAYOFYEAR, GETDATE()))   

Output