Convert From date and to date in Julian , JD Edwards in VBA

 

    Dim FrmDateImp

    Dim ToDateImp

    Dim tmpToDateImp

 

   

    FrmDateImp = Right(STR(PERIOD_BEG), 2) & "/01/" & Mid(str(PERIOD_BEG), 2, 4)

    tmpToDateImp = Right(STR(PERIOD_END), 2) & "/01/" & Mid(str(PERIOD_END), 2, 4)

    ToDateImp = DateSerial(YEAR(tmpToDateImp), Month(tmpToDateImp) + 1, 0)

 

   

    Dim FrmDate_DGJ As Long

    Dim ToDate_DGJ As Long

  FrmDate_DGJ = ((DatePart("YYYY", FrmDateImp) - 1900) * 1000) + DatePart("y", FrmDateImp)

ToDate_DGJ = ((DatePart("YYYY", ToDateImp) - 1900) * 1000) + DatePart("y", ToDateImp)

QryDateSummaryStr = "GLDGJ BETWEEN '" & FrmDate_DGJ & "' and '" & ToDate_DGJ & "'"

Change Date Format Excel Formula

 Change date format excel formula, this is good for developer too, who wants to create SQL statement from excel cell.

=TEXT(B3,"mm/dd/yyyy")

=TEXT(B3,"mm-dd-yyyy")

Dynamic SQL table columns for dates in Aging report, SQL Server

 

How to create Dynamic SQL table columns for dates in Aging report JD Edwards, SQL Server 

DECLARE @SQL_STR VARCHAR(8000)

DECLARE @DueDate VARCHAR(80)

DECLARE @DueDate14 VARCHAR(80)

DECLARE @DueDate28 VARCHAR(80)

DECLARE @DueDateAfter28 VARCHAR(80)

SET @DueDate =  '['+'Due as of ' +CONVERT(VARCHAR(10), GETDATE()+1, 101)+'],'

SET @DueDate14 =  '['+'Due as of ' +CONVERT(VARCHAR(10), GETDATE()+14, 101)+'],'

SET @DueDate28 =  '['+'Due as of ' +CONVERT(VARCHAR(10), GETDATE()+28, 101)+'],'

SET @DueDateAfter28 =  '['+'Due After ' +CONVERT(VARCHAR(10), GETDATE()+28, 101)+'],'

SET @SQL_STR = 

'SELECT RPAN8 AS Supplier_Number, sup.ABALPH AS Supplier_name, 

RPPST AS Pay_Stat, RPPYIN AS Pay_Inst, RPAN8 AS Payee_Number, PR.ABALPH AS Payee_Name,

RPDCT AS Doc_Type, RPDOC AS Doc_Number, RPSFX AS ITM, RPKCO AS CO, RPVINV AS Invoice_Number,

[dbo].[JDT_JTG](RPDIVJ) AS Invoice_Date,

[dbo].[JDT_JTG](RPDDJ) AS Due_Date,

CASE WHEN [dbo].[JDT_JTG](RPDDJ) BETWEEN CONVERT(VARCHAR(10), GETDATE(), 101) and  CONVERT(VARCHAR(10), GETDATE()+1, 101)

     THEN RPAG/100

ELSE 0 END '+ @DueDate + '

CASE WHEN [dbo].[JDT_JTG](RPDDJ) BETWEEN CONVERT(VARCHAR(10), GETDATE()+2, 101) and  CONVERT(VARCHAR(10), GETDATE()+14, 101)

     THEN RPAG/100

ELSE 0 END '+ @DueDate14 + '

CASE WHEN [dbo].[JDT_JTG](RPDDJ) BETWEEN CONVERT(VARCHAR(10), GETDATE()+15, 101) and  CONVERT(VARCHAR(10), GETDATE()+28, 101)

     THEN RPAG/100

ELSE 0 END '+ @DueDate28 + '

CASE WHEN [dbo].[JDT_JTG](RPDDJ) > CONVERT(VARCHAR(10), GETDATE()+28, 101) 

     THEN RPAG/100

ELSE 0 END AS '+ @DueDateAfter28 + '

CASE WHEN [dbo].[JDT_JTG](RPDDJ) >= CONVERT(VARCHAR(10), GETDATE(), 101) 

     THEN RPAG/100

ELSE 0 END AS Total_Due

FROM MFDB.PRODDTA.F0411

LEFT OUTER JOIN MFDB.PRODDTA.F0101 Sup ON Sup.ABAN8 = RPAN8

LEFT OUTER JOIN MFDB.PRODDTA.F0101 PR ON PR.ABAN8 = RPAN8

WHERE RPFY = 22 

--AND rpan8 = 642564 

AND RPPST = ''A'';

'

--PRINT (@SQL_STR)

EXEC (@SQL_STR)

Convert Calendar Date to Julian Date Excel VBA (JD Edwards style Date)

Convert Calendar Date to Julian Date Excel VBA (JD Edwards style Date)

   Dim myDate As Date

    myDate = "07/23/2022"

    Debug.Print (((DatePart("YYYY", myDate)) - 1900) * 1000) + DatePart("y", myDate)