Convert normal dates into SAP dates using VBA

When dealing with the underlying functionality of ABAP, there tends to be just one valid date format: YYYYMMDD. Slashes and dashes throw SAP's functions into conniptions and the many international formats only make things more confusing.

Converting regular date formats into YYYYMMDD in ABAP is easy enough, but handling it in VBA is even easier. This snippet also checks to see if there is a time embedded with the date separated by space and throws out the time.

Function SAPDate(strDate As String) As String
  Dim strTemp() As String
  If InStr(1, strDate, " ", vbTextCompare) Then
    strTemp() = Split(strDate, " ")
    strDate = strTemp(0)
  End If
  strTemp() = Split(strDate, "/")
  SAPDate = Right("0000" & strTemp(2), 4) & Right("00" & strTemp(0), 2) & Right("00" & strTemp(1), 2)
End Function

This function can be injected into VBA, SQL and queries to manage the dates before the source data is exported. Here are some examples of the SAPDate() function being used in SQL and in a query.

UPDATE tblMasterRecalls 
INNER JOIN tblRecallsHeader ON tblMasterRecalls.REFNO_RCL = tblRecallsHeader.[Recall#] 
SET tblMasterRecalls.RCL_VALID_FROM = SAPDate([tblRecallsHeader.Created]);