Wiki

BIZWEAVER

All

How to format dates and time for SAP Business One.

Working with dates and times when importing data into SAP Business One can be a little tricky. A specific format is required for the DI to understand what is being passed in.

For dates, use the following SQL statement:

CONVERT(VARCHAR,date_field,112) AS [Converted_Date]

This will create the date in YYYYMMDD format.

For times, use the following SQL statement:

RIGHT('00' + CAST(datepart(HOUR,date_field) AS VARCHAR),2) + RIGHT('00' + CAST(datepart(MINUTE,date_field) AS VARCHAR),2) AS [Converted_Time]

This will create the time in HHMM format, with zero padding as needed.

In both examples, “date_field” is the date field you need to convert. If the SAP field is just a date, the date format is fine. You do not need to provide a time value.& When an actual time value is needed, it will be in a separate field in the data record. This is the case where you will use the time format as shown.

HowTo

31 Jan 2018