Sap Hana where clause with date in DB is DEC15

[Steentje]

Dear All,

Ever wanted to write a query on a Hana db on a date selection.

In this blog post i share a query that selects on a date field 24 hours back in time.

So after you read this tiny article you should be able to select on DEC15 dates in a Hana DB.

i just want to share the following query that could help to easy select from the DB via a SQL query on a date time that is in the db as DECIMAL 15.

I also use following Hana functions that can be found in this guide

TO_VARCHAR, TO_DATE, ADD_SECONDS

For my example i use a table /SAPAPO/MATLWHST but the example works on any field in a table that has this DEC15 field.

Example /SAPAPO/MATLWHST table has the field CHANGEUTC

Se11 view

Lets say we want all changes done in the last 24 hours.

select
TO_VARCHAR(TO_DATE(ADD_SECONDS( CURRENT_UTCTIMESTAMP, 24*60*60*-1 ) ),'YYYYMMDDHHMMSS'),
CHANGEUTC,
TO_DATE( CAST( CHANGEUTC AS VARCHAR(14) ) , 'YYYYMMDDHH24MISS') as ChangeDateUTC,
TO_TIME( CAST( CHANGEUTC AS VARCHAR(14) ) , 'YYYYMMDDHH24MISS') as ChangeTimeUTC,
UTCTOLOCAL(TO_TIMESTAMP(CAST(CHANGEUTC AS VARCHAR(14)),'YYYYMMDDHH24MISS'),'CET') as ChangedAT
from "/SAPAPO/MATLWHST"
where

CHANGEUTC >=

TO_VARCHAR(

TO_DATE(

ADD_SECONDS( CURRENT_UTCTIMESTAMP, 24*60*60*-1 )

)

, 'YYYYMMDDHHMMSS'

)

Hope it will help you a bit as i could not easily find an example on the internet.

Greetings

Danny