Syntax for Date Variable in Sap business one Hana Database

Ramco Ramco

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Naimish Makwana1y ago
Accepted 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.
+1
Ramco Ramco1y ago
Hi Naimish

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

Thanks
0
Naimish Makwana1y ago
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
+1