When pushing down code from the application server to the database, of course you want to keep all the amenities that you have taken for granted when programming in ABAP on the application server. One of these amenities were the date and time handling capabilities that were available for the ABAP specific date and time types
d and
t together with the possibility of dealing with
timestamps. All that handling of dates, times and timestamps, e.g. the possibility of adding numbers to date or time fields, is ABAP specific. Now, when you push down your code to the database you might suddenly miss some of ABAP's possibilities. Therefore, the ABAP CDS and Open SQL teams together with the database interface teams work hard in order to provide you with as much of the former functionality as possible - and this in a platform independent way.
In release 7.50, ABAP CDS already offered a bunch of
date functions and time functions that allow you to deal with dates, times and timestamps on database level. In Open SQL these functions haven't been avaliable yet. Let's see what's in the box with release 7.51.
ABAP CDS
For 7.50 I was asked, when "
a filter on HANA column store for SY-DATUM would be available", and I said "
Sorry, not in 7.50. Maybe 7.51 …". And here we go. A new session variable
$session.system_date can be used in ABAP CDS. When accessing a CDS entity with Open SQL that variable contains the same value as you would expect from the sytem field
sy-datum in ABAP.
@AbapCatalog.sqlViewName: 'KELLERH_TST'
define view Kellerh_Test
as select from
tadir
{
pgmid,
object,
obj_name
}
where
created_on = $session.system_date
This CDS view retrieves all repository objects from table TADIR created today.
Furthermore, the set of date/time functions was enhanced with the following functions in ABAP CDS:
- TSTMP_TO_DATS
- TSTMP_TO_TIMS
- TSTMP_TO_DST
- DATS_TIMS_TO_TSTMP
- ABAP_SYSTEM_TIMEZONE
- ABAP_USER_TIMEZONE
The TSTMP_-functions carry out conversions of timestamps and the ABAP_-functions retrieve the sytem and user timezones of the current AS ABAP.
@AbapCatalog.sqlViewName: 'KELLERH_TST'
define view Kellerh_Test
as select from
demo_expressions
{
tstmp_to_dats( timestamp1,
abap_system_timezone( $session.client,'NULL' ),
$session.client,
'NULL' ) as dat,
tstmp_to_tims( timestamp1,
abap_system_timezone( $session.client,'NULL' ),
$session.client,
'NULL' ) as tim
}
This CDS view reads the column
timestamp1 of table
demo_expression and converts it to a date and a time. The time zone used for this is the system time zone retrieved by
abap_system_timezone for the current client taken from the session variable
$session.client.
Open SQL
With release 7.51, also Open SQL starts to participates in the date/time game and offers its first date functions:
- DATS_IS_VALID
- DATS_DAYS_BETWEEN
- DATS_ADD_DAYS
- DATS_ADD_MONTHS
These functions have been available in ABAP CDS with release 7.50 already and they do the same in Open SQL, of course.
SELECT FROM dokhl
FIELDS object,
dokversion,
dats_days_between( dokfdate, dokldate ) AS docu_age
WHERE id = 'SD' AND
object = 'ABAPCLASS' AND
langu = 'D'
ORDER BY docu_age DESCENDING
INTO TABLE @DATA(result).
This Open SQL
SELECT calculates the age of the versions of a document of the ABAP Keyword documentation in its original language.
For more information see:
