Days Between function SAP HANA

[former_member351438]

Hello Guys,

I'm trying to alert a query that will function if the U_DFrom table is less than 30 days to U_DTo table. Since Im new to Hana the usual Datediff will not work and im getting error using DAYS_BETWEEN function. Cannot find any example that the DATE_BETWEEN is in Where section.

Thanks for the help.

Here is my code.

SELECT T0."U_ComN", T0."U_ContN", T0."U_DFrom", T0."U_DTo", T0."U_Remarks" FROM "LIVEDB"."@ITDOCMAS" T0 WHERE DAYS_BETWEEN (TO_DATE (T0."U_DFrom", 'MM/DD/YYYY'), TO_DATE(T0."U_DTo", 'MM/DD/YYYY'))< '30'<br>

[michael_eaton3]

It's always helpful to provide the error message.

[michael_eaton3]

It is possible to use in the WHERE clause, for example

SELECT

*

FROM

tables

WHERE

DAYS_BETWEEN(CREATE_TIME, '2022-02-08 01:00:00') > 0

[raymond_giuseppi]

could you remove the quotes '30' -> 30 as days_between returns an integer ?

[former_member351438]

michael.eaton3

U_Dfrom and U_Dto are both UDF and as Date type.

This is the error.

[SAP AG][LIBODBCHDB DLL][HDBODBC] General Error;303 invalid DATE, TIME or TIMESTAMP value: exception 71000303: SQL Error 'Received Alerts' (OAIB)

[michael_eaton3]

It will work with a scalar user defined function. For example...

CREATE OR REPLACE FUNCTION my_time_udf
RETURNS retval DATE LANGUAGE SQLSCRIPT AS
BEGIN
  retval = current_timestamp;
END;

SELECT
	*
FROM
	tables
WHERE
	DAYS_BETWEEN(CREATE_TIME, my_time_udf()) > 0

[KonradZaleski]

The TO_DATE function is not used properly in your statement and that's why it returns an error. You should exclude the formatting 'MM/DD/YYYY'

WHERE DAYS_BETWEEN (TO_DATE (T0."U_DFrom"), TO_DATE(T0."U_DTo"))

Just make sure that columns U_DFrom and U_DTo store valid date values

[SonTran]

Hi,

Try this:

WHERE DAYS_BETWEEN (T0."U_DFrom", T0."U_DTo") < 30;

Hope this helps,

Son Tran

[yogananda]

clay005

You don't need to use the DATE_BETWEEN function for this. Instead, use the DATEDIFF function.

SELECT 
  U_DFrom,
  U_DTo
FROM 
  Table
WHERE
  DATEDIFF('DAY', U_DFrom, U_DTo) < 30

The above query will return all rows where the difference between the two column values is less than 30 days.

[michael_eaton3]

A DATEDIFF function does not exist in any supported version of SAP HANA.

[former_member351438]

This will not work in SAP Hana. I just wish SAP keep it that way.

[wenxi_liu]

Me too. I hope that SAP hana can support DATEDIFF function.How do you calculate the date difference without DATEDIFF?