SQL Server 2016 Hosting – HostForLIFE.eu :: 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

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

Output

2

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

3