Question Re: Days Between function SAP HANA in Technology Q&A

Days Between function SAP HANA

former_member351438 — Thu, 09 Feb 2023 08:51:26 GMT

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>

Re: Days Between function SAP HANA

michael_eaton3 — Thu, 09 Feb 2023 09:43:33 GMT

It's always helpful to provide the error message.

Re: Days Between function SAP HANA

Yogananda — Thu, 09 Feb 2023 09:46:21 GMT

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.

Re: Days Between function SAP HANA

michael_eaton3 — Thu, 09 Feb 2023 09:52:42 GMT

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

Re: Days Between function SAP HANA

michael_eaton3 — Thu, 09 Feb 2023 10:00:48 GMT

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

Re: Days Between function SAP HANA

RaymondGiuseppi — Thu, 09 Feb 2023 11:33:47 GMT

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

Re: Days Between function SAP HANA

former_member351438 — Fri, 10 Feb 2023 06:15:32 GMT

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)

Re: Days Between function SAP HANA

former_member351438 — Fri, 10 Feb 2023 06:21:51 GMT

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

Re: Days Between function SAP HANA

michael_eaton3 — Fri, 10 Feb 2023 07:59:27 GMT

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

Re: Days Between function SAP HANA

wenxi_liu49 — Wed, 15 Feb 2023 03:11:43 GMT

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

Re: Days Between function SAP HANA

KonradZaleski — Wed, 22 Feb 2023 15:49:54 GMT

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

Re: Days Between function SAP HANA

SonTran — Thu, 23 Feb 2023 10:47:41 GMT

Hi,

Try this:

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

Hope this helps,

Son Tran