Sap Hana where clause with date in DB is DEC15
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
Lets say we want all changes done in the last 24 hours.
TO_VARCHAR(TO_DATE(ADD_SECONDS( CURRENT_UTCTIMESTAMP, 24*60*60*-1 ) ),’YYYYMMDDHHMMSS’),
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
ADD_SECONDS( CURRENT_UTCTIMESTAMP, 24*60*60*-1 )
Hope it will help you a bit as i could not easily find an example on the internet.