Syntax for Date Variable in Sap business one Hana Database

Question

Hi I have String Datatype for Date variable in Crystal Reports. What should be the query syntax for OINV table in a Stored Procedure in Sap B1 Hana Database. Thanks

Naimish Makwana · Answer

If the user enters the date in the format dd/MM/yyyy  you'll need to adjust the TO_DATE function to match this format. Here's how you can modify the stored procedure to handle the dd/MM/yyyy format: CREATE PROCEDURE GetInvoicesByDate (IN dateString NVARCHAR(10)) LANGUAGE SQLSCRIPT AS BEGIN DECLARE dateValue DATE; dateValue := TO_DATE(dateString 'DD/MM/YYYY'); SELECT * FROM OINV WHERE DocDate = dateValue; END; In this example TO_DATE(dateString 'DD/MM/YYYY') converts the string to a date format that matches the dd/MM/yyyy input. This should work correctly for a date like 01/04/2024 .

Naimish Makwana · Answer

To handle a date variable stored as a string in Crystal Reports and query the OINV table in a stored procedure for SAP B1 HANA you can convert the string to a date format within your query. Here's an example of how you can do this: CREATE PROCEDURE GetInvoicesByDate (IN dateString NVARCHAR(10)) LANGUAGE SQLSCRIPT AS BEGIN DECLARE dateValue DATE; dateValue := TO_DATE(dateString 'YYYY-MM-DD'); SELECT * FROM OINV WHERE DocDate = dateValue; END; In this example: dateString is the input parameter of type NVARCHAR(10) which holds the date as a string. TO_DATE(dateString 'YYYY-MM-DD') converts the string to a date format. The query then selects records from the OINV table where the DocDate matches the converted date. Make sure to adjust the date format in TO_DATE function according to the format of your date string. Thanks

Ramco Ramco · Answer

Hi Naimish If user enters 01/04/2024 . Will it work. User has entered date in dd/MM/yyyy. Thanks