ABAP News for Release 7.51 – Date and Time in ABAP CDS and Open SQL
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:
I feel we need a system fields available in LHS of Open SQl, if we intended to achieve code push. For an eg - considering on month, derive a value / calculate value from DB.
Found a cheat though..
DELETE FROM demo_expressions.
INSERT demo_expressions FROM @( VALUE #( id = '1' ) ).
SELECT CASE substring( a~datum,5,2 )
WHEN '01' THEN Jan
WHEN '02' Then 'Feb'
FROM demo_cds_system_fields AS a
JOIN Table_B AS b ON 1 = 1 INTO @DATA(Month).
Do we have any other option which i am not aware of or anything planned for future release?
Is there any other option in Open sql to to return a value based on current month??
I used demo_cds_system_fields but feel it is not right option.
SELECT CASE substring( a~datum,5,2 )
WHEN ’01’ THEN Jan
WHEN ’02’ Then ‘Feb’
FROM demo_cds_system_fields AS a
INTO @DATA(Month).
Hi Horst,
Is there an easy way to get the day of the week in the CDS view? I can get the system date and I can convert it but I can’t find any documentation on how to get the day of the week. I need this and then I need to calculate the Monday of that week and the Sunday of the week. This is easy in ABAP but I can’t find a way in CDS.
I looked through ABAP CDS – Date Functions
Basically, I am trying to do something like this with the system date or a parameter that takes in a date:
SELECT DAYNAME ('2017-09-04') "DayName" FROM DUMMY;
Result:
MONDAY
‘Thanks,
Jay
Maybe table SCAL_TT_DATE can help you
It is handy with built in variables, would it also possible to use #SYSTEM_DATE and $session.system_date as a substring say to an annotation?
Say you want to create a report for the current fiscal year and previous month as default, so you add year and period as default filters below hardcoded period 2 of 2020:
@Consumption.filter.defaultValue: '2020'
@EndUserText.label: 'Fiscal year'
@UI.selectionField.position: 1
gjahr,
@Consumption.filter.defaultValue: '002'
@EndUserText.label: 'Period'
@UI.selectionField.position: 2
poper,-_
But what you really want to do is to have the current year, and current period.
In theory something like @Consumption.filter.defaultValue: #SYSTEM_DATE(4) or substring(session.system_date,4,4)