1.说明
本文介绍在CDS视图中使用日期相关功能的一些方法。CDS视图是功能强大的视图。如果您使用的是SAP S / 4HANA系统，那么CDS对于技术支持非常重要，日期的处理与在ABAP中一有定差异，以下作为一些参考。
2. DATE_IS_VALID
日期函数，DATE_IS_VALID，用于验证包含有效SAP日期格式“ YYYYMMDD ”的日期。如果日期采用有效的日期格式，则返回“ 1 ”，否则返回“ 0 ”。如果日期为空，则返回“ 0 ”。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
@AbapCatalog.sqlViewName: 'ZCDS_DATE' @AbapCatalog.compiler.compareFilter: true @AccessControl.authorizationCheck: #CHECK @EndUserText.label: 'Date Functions' define view zcds_date_functions with parameters p_from_date:abap.dats as select from snwd_so { snwd_so.buyer_guid, snwd_so.billing_status, // returns 1 - Valid Date // returns 0 - Invalid Date DATS_IS_VALID(:p_from_date) as from_date } |
3.DATS_DAYS_BETWEEN
日期函数DATS_DAYS_BETWEEN计算两个指定日期date1和date2之间的天数。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
@AbapCatalog.sqlViewName: 'ZCDS_DATE' @AbapCatalog.compiler.compareFilter: true @AccessControl.authorizationCheck: #CHECK @EndUserText.label: 'Date Functions' define view zcds_date_functions as select from vbak { vbak.vbeln, //Sales Document vbak.auart, //Sales Document Type vbak.audat, //Document Date vbak.vdatu, //Requested delivery date DATS_DAYS_BETWEEN(audat, vdatu) as no_of_days } |
4.DATS_ADD_DAYS
DATS_ADD_DAYS(date, days, on_error)
日期DATS_ADD_DAYS将天添加到指定的日期日期。天应该是INT4类型，允许天的负值和正值
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
@AbapCatalog.sqlViewName: 'ZCDS_DATE' @AbapCatalog.compiler.compareFilter: true @AccessControl.authorizationCheck: #CHECK @EndUserText.label: 'Date Functions' define view zcds_date_functions as select from vbak { vbak.vbeln, //Sales Document vbak.auart, //Sales Document Type vbak.audat, //Document Date vbak.vdatu, //Requested delivery date DATS_ADD_DAYS(vdatu, 10, 'NULL') as option2, //add 10 days DATS_ADD_DAYS(vdatu, -10, 'NULL') as option1, //substract 10 days DATS_ADD_DAYS(vdatu, 5, 'FAIL') as option3, DATS_ADD_DAYS(vdatu, 4, 'INITIAL') as option4, DATS_ADD_DAYS(vdatu, 2, 'UNCHANGED') as option5 } |
5.DATS_ADD_MONTHS
DATS_ADD_MONTHS(date, months, on_error)
The date function DATS_ADD_MONTHS add months months to the specified date date.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
@AbapCatalog.sqlViewName: 'ZCDS_DATE' @AbapCatalog.compiler.compareFilter: true @AccessControl.authorizationCheck: #CHECK @EndUserText.label: 'Date Functions' define view zcds_date_functions as select from vbak { vbak.vbeln, //Sales Document vbak.auart, //Sales Document Type vbak.audat, //Document Date vbak.vdatu, //Requested delivery date DATS_ADD_MONTHS(vdatu, 10, 'NULL') as option1, //add 10 months DATS_ADD_MONTHS(vdatu, -10, 'NULL') as option2, //substract 10 months DATS_ADD_MONTHS(vdatu, 5, 'FAIL') as option3, DATS_ADD_MONTHS(vdatu, 4, 'INITIAL') as option4, DATS_ADD_MONTHS(vdatu, 2, 'UNCHANGED') as option5 } |
6.从日期和时间计算CDS视图中的时间戳
通过转换函数DATS_TIMS_TO_TSTMP将数据库表DEMO的ZDATE和ZTIME列的值组合为时间戳
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
@AbapCatalog.sqlViewName: 'DEMO_CDS_DATTYM' @AbapCatalog.compiler.compareFilter: true @AccessControl.authorizationCheck: #NOT_REQUIRED @EndUserText.label: 'DEMO' @VDM.viewType: #BASIC @Analytics.dataCategory: #FACT @Analytics.dataExtraction.enabled: true Define view demo_cds_date_time as select from demo { ZDATE, ZTIME, DATS_TIMS_TO_TSTMP (ZDATE, ZTIME, abap_system_timezone($session.client,'NULL' ), $session.client,'NULL') as ZTIMESTAMP } |
7.将HH：MM：SS中指定的时间转换为秒
通过强制转换和子字符串功能，我们将HH：MM：SS中的时间转换为秒。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
@AbapCatalog.sqlViewName: 'DEMO_CDS_DATTYM' @AbapCatalog.compiler.compareFilter: true @AccessControl.authorizationCheck: #NOT_REQUIRED @EndUserText.label: 'DEMO' @VDM.viewType: #BASIC @Analytics.dataCategory: #FACT @Analytics.dataExtraction.enabled: true Define view demo_cds_date_time as select from demo { ZTIME, cast( cast(cast(substring(ZTIME,1,2) as abap.numc( 2 )) as abap.dec( 15, 0 )) * 3600 + cast(cast(substring(ZTIME,3,2) as abap.numc( 2 )) as abap.dec( 15, 0 )) * 60 + cast(cast(substring(ZTIME,5,2) as abap.numc( 2 )) as abap.dec( 15, 0 )) as abap.dec( 15, 0 ))as ZSECONDS } |
8.两个日期和时间字段之间的秒数
函数TSTMP_SECONDS_BETWEEN计算两个指定时间戳之间的时差。因此，在本例中，我们将在函数TSTMP_SECONDS_BETWEEN中创建时间戳。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
@AbapCatalog.sqlViewName: 'DEMO_CDS_DATTYM' @AbapCatalog.compiler.compareFilter: true @AccessControl.authorizationCheck: #NOT_REQUIRED @EndUserText.label: 'DEMO' @VDM.viewType: #BASIC @Analytics.dataCategory: #FACT @Analytics.dataExtraction.enabled: true Define view demo_cds_date_time as select from demo { ZDATE, ZTIME, ZDATE1, ZTIME1, tstmp_seconds_between ( dats_tims_to_tstmp ( ZDATE, ZTIME, abap_system_timezone( $session.client,'NULL' ), $session.client, 'NULL' ), dats_tims_to_tstmp ( ZDATE1, ZTIME2, abap_system_timezone( $session.client,'NULL' ), $session.client, 'NULL' ), 'NULL' )as ZDIFF_SECONDS } |
9.在日期中添加天以创建新日期
函数 DATS_ADD_DAYS将天添加到指定日期。如您在下面的代码中看到的，+ 1是我要添加日期的天数。您可以根据需要添加。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
@AbapCatalog.sqlViewName: 'DEMO_CDS_DATTYM' @AbapCatalog.compiler.compareFilter: true @AccessControl.authorizationCheck: #NOT_REQUIRED @EndUserText.label: 'DEMO' @VDM.viewType: #BASIC @Analytics.dataCategory: #FACT @Analytics.dataExtraction.enabled: true Define view demo_cds_date_time as select from demo { ZDATE, dats_add_days(ZDATE, +1, 'NULL') as ZDATE_NEW } |
10.将日期从YYYYMMDD转换为DD / MM / YYYY
通过强制转换和连续功能，我们将以DD / MM / YYYY格式创建日期。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
@AbapCatalog.sqlViewName: 'DEMO_CDS_DATTYM' @AbapCatalog.compiler.compareFilter: true @AccessControl.authorizationCheck: #NOT_REQUIRED @EndUserText.label: 'DEMO' @VDM.viewType: #BASIC @Analytics.dataCategory: #FACT @Analytics.dataExtraction.enabled: true Define view demo_cds_date_time as select from demo { Date_column, Cast( Concat( Concat( Concat(substring(Date_Column, 5, 2), ‘/’), Concat(substring(Date_Column, 7, 2), ‘/’), ), Substring(Date_column, 1, 4) ) As char10 preserving type) as ZCONVERTED_DATE } |
11.系统日期功能
会话变量$session。在CDS视图中使用system_date提供对当前系统日期的直接访问。使用此功能可以给您系统日期。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
@AbapCatalog.sqlViewName: 'DEMO_CDS_DATTYM' @AbapCatalog.compiler.compareFilter: true @AccessControl.authorizationCheck: #NOT_REQUIRED @EndUserText.label: 'DEMO' @VDM.viewType: #BASIC @Analytics.dataCategory: #FACT @Analytics.dataExtraction.enabled: true Define view demo_cds_date_time as select from demo { $session.system_date as systemdate } |
12.显示当前系统时间戳
函数tstmp_current_utctimestamp（）用于显示系统时间戳。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
@AbapCatalog.sqlViewName: 'DEMO_CDS_DATTYM' @AbapCatalog.compiler.compareFilter: true @AccessControl.authorizationCheck: #NOT_REQUIRED @EndUserText.label: 'DEMO' @VDM.viewType: #BASIC @Analytics.dataCategory: #FACT @Analytics.dataExtraction.enabled: true Define view demo_cds_date_time as select from demo { tstmp_current_utctimestamp() } |
其它日期时间
|
1 2 3 4 5 6 7 8 9 10 11 12 |
... TSTMP_TO_DATS(tstmp,tzone,clnt,on_error) | TSTMP_TO_TIMS(tstmp,tzone,clnt,on_error) | TSTMP_TO_DST(tstmp,tzone,clnt,on_error) | DATS_TIMS_TO_TSTMP(date,time,tzone,clnt,on_error) | TSTMPL_TO_UTCL(tstmpl,on_error,on_initial) | TSTMPL_FROM_UTCL(utcl,on_null) | DATS_TO_DATN(dats,on_error,on_initial) | DATS_FROM_DATN(date,on_null) | TIMS_TO_TIMN(tims,on_error) | TIMS_FROM_TIMN(time,on_null) ... |
|
1 2 3 4 |
//创建日期 cast ( TSTMP_TO_DATS(cast( createdat as abap.dec( 15, 0 ) ),abap_system_timezone($session.client,'NULL' ), $session.client,'NULL') as datum ) as erdat1, //创建时间 cast ( TSTMP_TO_TIMS(cast( createdat as abap.dec( 15, 0 ) ),abap_system_timezone($session.client,'NULL' ), $session.client,'NULL') as uzeit ) as erzet1, |
