SQL Declarations in Data Adapter

In the end, I had to do some research about JDE's SQL in particular. I was able to get it to work with a few minor adjustments. In the end, I decided to handle the date conversion using a couple of XFBR rules, which greatly simplified the SQL.

SELECT
CXCRCD as "CURR FROM",
CXCRDC as "CURR TO",
Round(CXCRR,5) as Multiplier,
Round(CXCRRD,5) as Divisor
from
PRODDTA.F0015
where
CXCRDC = 'USD'
and CXEFT>= 'XFBR(MFC_FX_Date_Converter, MonthStart,TimeMe=[|!MFC_FX_ML_Select_Time!|])'
AND CXEFT<= 'XFBR(MFC_FX_Date_Converter, MonthEnd,TimeMe=[|!MFC_FX_ML_Select_Time!|])'
ORDER BY
CXCRCD,CXEFT

The XFBR looks like this:

Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardStringFunctionArgs) As Object
Try
'Parameter Example: XFBR(MFC_FX_Date_Converter, MonthStart,TimeMe=[TimeSelector])
If args.FunctionName.XFEqualsIgnoreCase("MonthStart") Then
Dim TimeMe As String = args.NameValuePairs("TimeMe")
Dim StartTime As String = Me.Month_Start(si,TimeMe)

Return StartTime
End If

'Parameter Example: XFBR(MFC_FX_Date_Converter, MonthEnd,TimeMe=[TimeSelector])
If args.FunctionName.XFEqualsIgnoreCase("MonthEnd") Then
Dim TimeMe As String = args.NameValuePairs("TimeMe")
Dim EndTime As String = Me.Month_End(si,TimeMe)

Return EndTime
End If

Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function

Public Function Month_Start(ByVal si As SessionInfo, ByVal TimeMe As String )
Try
Dim TimeLength As Integer = TimeMe.Length
Dim MosCount As Integer
If TimeLength = 7 Then MosCount = 2 Else MosCount = 1
Dim YearRun As Integer = LEFT(TimeMe, 4)
Dim YearPart As Integer = YearRun.ToString.Substring(YearRun.ToString.Length - 2)
Dim MonthRun As Integer = TimeMe.Substring(5,MosCount)
Dim EndTime As String = Nothing
Dim Dayz As Integer = Nothing
Dim iDayNumber As Integer = Nothing

Dim booIsLeapYear As Boolean
booIsLeapYear = ((YearRun Mod 4 = 0) And (YearRun Mod 100 <> 0)) Or (YearRun Mod 400 = 0)

Dim dt As Date = MonthRun & "/1/" & YearRun

If debugSwitch = True Then brapi.ErrorLog.LogMessage(si, "TimeMe= " & TimeMe & " YearRun= " & YearRun & " YearPart= " & YearPart & " dt= " & dt & " iDayNumber= " & iDayNumber)

iDayNumber = DateDiff("d", CDate("1/1/" & Year(dt)), dt) + 1

EndTime = "1" & YearPart & iDayNumber

Return EndTime
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try

End Function

Public Function Month_End(ByVal si As SessionInfo, ByVal TimeMe As String )
Try
Dim TimeLength As Integer = TimeMe.Length
Dim MosCount As Integer
If TimeLength = 7 Then MosCount = 2 Else MosCount = 1
Dim YearRun As Integer = LEFT(TimeMe, 4)
Dim YearPart As Integer = YearRun.ToString.Substring(YearRun.ToString.Length - 2)
Dim MonthRun As Integer = TimeMe.Substring(5,MosCount)
Dim EndTime As String = Nothing
Dim Dayz As Integer = Nothing
Dim iDayNumber As Integer = Nothing

Dim booIsLeapYear As Boolean
booIsLeapYear = ((YearRun Mod 4 = 0) And (YearRun Mod 100 <> 0)) Or (YearRun Mod 400 = 0)

Select Case MonthRun
Case 1, 3, 5, 7, 8, 10, 12
Dayz = 31
Case 2
If booIsLeapYear = True Then
Dayz = 29
Else
Dayz = 28
End If
Case Else
Dayz = 30
End Select

Dim dt As Date = MonthRun & "/" & Dayz & "/" & YearRun

If debugSwitch = True Then brapi.ErrorLog.LogMessage(si, "TimeMe= " & TimeMe & " YearRun= " & YearRun & " YearPart= " & YearPart & " dt= " & dt & " iDayNumber= " & iDayNumber)

iDayNumber = DateDiff("d", CDate("1/1/" & Year(dt)), dt) + 1

EndTime = "1" & YearPart & iDayNumber

Return EndTime
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try

End Function