Tell us what you think

We want to create the best possible experience for our learners. Your feedback helps us to improve the experience for everyone.

SAP Learning Journey

Using Special Built-in Functions in ABAP SQL

Objectives

After completing this lesson, you will be able to:

Process dates, times, and timestamps on the database
Use built-in conversions functions

Functions for Processing Dates, Times, and Timestamps

SQL Functions for Date, Time, and Time Stamp

ABAP SQL offers a huge variety of built-in functions for date, time, and timestamp information. There are functions to check whether the content of a field is valid, functions for calculations, or functions to extract certain information from an input argument. Some of these functions are listed here. A complete list can be found in the ABAP documentation.

Some of the functions are type-specific. The names of these functions start with a prefix that identifies the type they request as input. Functions starting with DATS_ require input of type DATS, functions starting with DATN_ require input of type DATN, and so on.

Other functions are generic. They can handle input of different types. Function IS_VALID, for example, can check the validity of a date, a point in time, or a time stamp. It accepts both date formats (DATS and DATN) and both time formats (TIMS and TIMN). For time stamps, however, only type UTCLONG is supported.

In some cases, you can choose between a type-specific function and a generic function. For a field of type DATS, for example, you can either use DATS_IS_VALID( ) or IS_VALID( ). You should prefer the newer, generic functions.

Note

The generic functions do not support time stamps of type TIMESTAMP and TIMESTAMPL, at present. For these types, you have to use the type-specific functions starting with TSTMP_.

Here is an example of the usage of generic functions with date-like information. Like most other SQL expressions, you can use the functions in the WHERE-clause, too. Here, we restrict the selection to travels that exceed 10 days.

Note

Function IS_VALID( ) is not a real boolean function. It returns integer value 1 for true and 0 for false.

Try It Out: Date processing

Create a new global class that implements interface IF_OO_ADT_CLASSRUN.

Copy the following code snippet to the implementation part of method if_oo_adt_classrun~main( ):

Code snippet


    SELECT FROM /dmo/travel
         FIELDS begin_date,
                end_date,
                is_valid( begin_date  )              AS valid,

                add_days( begin_date, 7 )            AS add_7_days,
                add_months(  begin_date, 3 )         AS add_3_months,
                days_between( begin_date, end_date ) AS duration,

                weekday(  begin_date  )              AS weekday,
                extract_month(  begin_date )         AS month,
                dayname(  begin_date )               AS day_name

          WHERE customer_id = '000001'
            AND days_between( begin_date, end_date ) > 10

           INTO TABLE @DATA(result).

    out->write(
      EXPORTING
        data   = result
        name   = 'RESULT'
    ).

Expand

Select CTRL + F3 to activate the class and F9 to execute it as a console app.
Analyze the console output.
Place the cursor on one of the date processing functions (for example weekday and select F1 to open the documentation.
Look for other date processing functions in the documentation and try them out to get familiar with their possibilities.

Conversion Functions

Some ABAP SQL Conversion Functions

Not all type conversions can be handled by the CAST expression. For some conversions, ABAP SQL offers specific conversion functions as shown in the figure below.

An example is SQL function DATS_FROM_DATN, which performs the conversion from DATN, the DB-specific date representation, to DATS, the ABAP specific char-like date format. SQL function DATS_TO_DATN( ) does the conversion in the other direction.

Functions TMSTMP_TO_DATS( ) and TMSTMP_TO_TIMS( ) convert a time stamp into a date field and a time field, respectively. Note that these functions are only available for timestamps of technical format DEC( 15, 0). Other time stamp formats need to be converted before they can serve as input for these functions. Function DATS_TIMS_TO_TMSTMP combines a date and a time information into one time stamp.

A special group of conversion functions allow you to do conversions between different measurement units and currencies. Function UNIT_CONVERSION( ) coverts a quantity from a source unit to a target unit, for example, a distance in kilometers to the same distance in miles. Function CURRENCY_CONVERSION( ) converts an amount in one currency into the same amount in another currency on a specific reporting date.

Note

For CURRENCY_CONVERSION( ) to work properly, the time dependent exchange rates have to be available in the database of your system. See the SAP system documentation for details on how to do that.

Example: SQL Function for Time Stamp Conversion

This example reads a time stamp from table /DMO/TRAVEL (field LASTCHANGEDAT) and converts it to the date and time in time zone 'EST'.

The time stamp is stored in the database table as type TIMESTAMPL, that is, with 7 decimals. This means it has to be converted into a short time stamp without decimals, to make it compatible with input parameter TSTMP of the SQL functions. Literal 'EST' also needs to be converted to be compatible with input parameter TZONE.

Note

There is a time shift of 4 hours between Universal Time and Eastern Standard Time.

Example: SQL Function for Unit Conversion

In this example, the distance information of flight connections is converted to miles (MI), while in the database, the information is stored in kilometers (KM).

Because table field DISTANCE is defined with type INT4 instead of type QUAN, it has to be converted to match the type of input parameter QUANTITY.

Literal 'MI' has to be converted, too, because it is of type CHAR(2) and not of type 'UNIT'.

Note

The example only supplies the three mandatory parameters of UNIT_CONVERSION( ). A list of the optional parameters and their meaning can be found in the ABAP documentation.

In this example, the total price of a travel is converted into the target currency Euro. Function CURRENCY_CONVERSION is supplied with the amount (field TOTAL_PRICE), the source currency (field CURRENCY_CODE), the target_currency (Literal 'EUR'), and the exchange rate date (ABAP variable TODAY). Before the SELECT statement, variable TODAY is filled in a method call with the system date.

Note

The example only supplies the four mandatory parameters of CURRENCY_CONVERSION( ). A list of the optional parameters and their meaning can be found in the ABAP documentation.

Try It Out: Conversion functions

Create a new global class that implements interface IF_OO_ADT_CLASSRUN.

Copy the following code snippet to the implementation part of method if_oo_adt_classrun~main( ):

Code snippet


  SELECT FROM /dmo/travel
         FIELDS lastchangedat,
                CAST( lastchangedat AS DEC( 15,0 ) ) AS latstchangedat_short,

                tstmp_to_dats( tstmp = CAST( lastchangedat AS DEC( 15,0 ) ),
                               tzone = CAST( 'EST' AS CHAR( 6 ) )
                             ) AS date_est,
                tstmp_to_tims( tstmp = CAST( lastchangedat AS DEC( 15,0 ) ),
                               tzone = CAST( 'EST' AS CHAR( 6 ) )
                             ) AS time_est

          WHERE customer_id = '000001'
           INTO TABLE @DATA(result_date_time).

    out->write(
      EXPORTING
        data   = result_date_time
        name   = 'RESULT_DATE_TIME'
    ).


*********************************************************************

    DATA(today) = cl_abap_context_info=>get_system_date(  ).

    SELECT FROM /dmo/travel
         FIELDS total_price,
                currency_code,

                currency_conversion( amount             = total_price,
                                     source_currency    = currency_code,
                                     target_currency    = 'EUR',
                                     exchange_rate_date = @today
                                   ) AS total_price_EUR

          WHERE customer_id = '000001' AND currency_code <> 'EUR'
           INTO TABLE @DATA(result_currency).

    out->write(
      EXPORTING
        data   = result_currency
        name   = 'RESULT__CURRENCY'
    ).


**********************************************************************

    SELECT FROM /dmo/connection
         FIELDS distance,
                distance_unit,
                unit_conversion( quantity = CAST( distance AS QUAN ),
                                 source_unit = distance_unit,
                                 target_unit = CAST( 'MI' AS UNIT ) )  AS distance_MI

          WHERE airport_from_id = 'FRA'
           INTO TABLE @DATA(result_unit).

    out->write(
      EXPORTING
        data   = result_unit
        name   = 'RESULT_UNIT'
    ).

Expand

Select CTRL + F3 to activate the class and F9 to execute it as a console app.
Analyze the console output.

Next lesson