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

If you want to store date information in a database field, you can choose from two types:

  • DATS stores the date as it is processed in ABAP. That means, the date is stored as a character of length 8 in the format 'YYYYMMDD'.
  • DATN stores the information in the date type of the database. When reading the information from the database with ABAP SQL, it is automatically converted back to the 8-digits format.

The same difference exists between types TIMS and TIMN.

If you want to store time stamp information in a database field, you can choose from three types:

  • TIMESTAMP stores the information as a number without decimals, where the digits of the number represent the year, month, day, hours, minutes, and seconds.
  • TIMESTAMPL is the same as TIMESTAMP, but in addition stores up to 7 decimals which corresponds to a precision of 100 nanoseconds.

  • UTCLONG has the same precision as TIMESTAMPL but stores the information as an integer of 8 byte length.

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

  1. Create a new global class that implements interface IF_OO_ADT_CLASSRUN.
  2. Copy the following code snippet to the implementation part of method if_oo_adt_classrun~main( ):
    Code Snippet
    Copy code
    Switch to dark mode
    12345678910111213141516171819202122232425
    
    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'
    ).
  3. Select CTRL + F3 to activate the class and F9 to execute it as a console app.
  4. Analyze the console output.
  5. Place the cursor on one of the date processing functions (for example weekday and select F1 to open the documentation.
  6. 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

  1. Create a new global class that implements interface IF_OO_ADT_CLASSRUN.
  2. Copy the following code snippet to the implementation part of method if_oo_adt_classrun~main( ):
    Code Snippet
    Copy code
    Switch to dark mode
    12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364
    
  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'
    ).
  3. Select CTRL + F3 to activate the class and F9 to execute it as a console app.
  4. Analyze the console output.

Use Special Built-in Functions in ABAP SQL

In this exercise, you push the calculation of the flight duration and the currency conversion down to the database.

Template:

  • /LRN/CL_S4D401_DBS_PUSH_DOWN (Global Class)

Solution:

  • /LRN/CL_S4D401_DBS_FUNCTIONS (Global Class)

Task 1: Copy Template (Optional)

Copy the template class. If you finished the previous exercise, you can skip this task and continue editing your class ZCL_##_SOLUTION.

Steps

  1. Copy class /LRN/CL_S4D401_DBS_PUSH_DOWN to a class in your own package (suggested name: ZCL_##_SOLUTION, where ## stands for your group number).

    1. In the Project Explorer view, right-click class /LRN/CL_S4D401_DBS_PUSH_DOWN to open the context menu.

    2. From the context menu, choose Duplicate ....

    3. Enter the name of your package in the Package field. In the Name field, enter the name ZCL_##_SOLUTION, where ## stands for your group number.

    4. Adjust the description and choose Next.

    5. Confirm the transport request and choose Finish.

  2. Activate the copy.

    1. Press Ctrl + F3 to activate the class.

Task 2: Calculate with Timestamps

In local class LCL_PASSENGER_FLIGHT, adjust the implementation of the CLASS_CONSTRUCTOR method. Move the calculation of the flight duration to the field list of the SELECT statement and store the result in the duration component of the connections_buffer attribute.

Steps

  1. In local class LCL_PASSENGER_FLIGHT, navigate to the implementation of the CLASS_CONSTRUCTOR method.

    1. Perform this step as before.

  2. At the end of the field list of the SELECT statement, add a call to a suitable SQL function that calculates the difference in seconds between two timestamps.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      12345678910111213
      
  SELECT
    FROM /lrn/connection AS c
    LEFT OUTER JOIN /lrn/airport AS f
      ON c~airport_from_id = f~airport_id
    LEFT OUTER JOIN /lrn/airport AS t
      ON c~airport_to_id = t~airport_id
  FIELDS carrier_id, connection_id,
         airport_from_id, airport_to_id, departure_time, arrival_time,
         f~timzone AS timezone_from,
         t~timzone AS timezone_to,
      INTO TABLE @connections_buffer.

  3. As the first argument (parameter tstmp1), call a suitable SQL function that calculates a time stamp based on the current date, the departure time and the timezone of the departure airport.

    Hint

    You have to move the call of functional method cl_abap_context_info=>get_system_date to before the SELECT statement so that you can access the current date in the SELECT statement.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      12345678910111213141516
         
  SELECT
    FROM /lrn/connection AS c
    LEFT OUTER JOIN /lrn/airport AS f
      ON c~airport_from_id = f~airport_id
    LEFT OUTER JOIN /lrn/airport AS t
      ON c~airport_to_id = t~airport_id
  FIELDS carrier_id, connection_id,
         airport_from_id, airport_to_id, departure_time, arrival_time,
         f~timzone AS timezone_from,
         t~timzone AS timezone_to,
         tstmp_seconds_between(
                              )

      INTO TABLE @connections_buffer.

  4. Call the same function to calculate the second time stamp (parameter tstmp2).

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      12345678910111213141516171819202122
      
  DATA(today) = cl_abap_context_info=>get_system_date( ).
   
  SELECT
    FROM /lrn/connection AS c
    LEFT OUTER JOIN /lrn/airport AS f
      ON c~airport_from_id = f~airport_id
    LEFT OUTER JOIN /lrn/airport AS t
      ON c~airport_to_id = t~airport_id
  FIELDS carrier_id, connection_id,
         airport_from_id, airport_to_id, departure_time, arrival_time,
         f~timzone AS timezone_from,
         t~timzone AS timezone_to,
         tstmp_seconds_between(
           tstmp1 = dats_tims_to_tstmp(
                    date = @today,
                    time = c~departure_time,
                    tzone = f~timzone ),                   
                              )

      INTO TABLE @connections_buffer.

  5. Because we want to have the duration in minutes not in seconds, divide the number of seconds by 60.

    Hint

    Use a suitable arithmetic SQL function that returns the division result as integer.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      12345678910111213141516171819202122232425
      
  DATA(today) = cl_abap_context_info=>get_system_date( ).
   
  SELECT
    FROM /lrn/connection AS c
    LEFT OUTER JOIN /lrn/airport AS f
      ON c~airport_from_id = f~airport_id
    LEFT OUTER JOIN /lrn/airport AS t
      ON c~airport_to_id = t~airport_id
  FIELDS carrier_id, connection_id,
         airport_from_id, airport_to_id, departure_time, arrival_time,
         f~timzone AS timezone_from,
         t~timzone AS timezone_to,
             tstmp_seconds_between(
               tstmp1 = dats_tims_to_tstmp(
                          date = @today,
                          time = c~departure_time,
                          tzone = f~timzone ),
               tstmp2 = dats_tims_to_tstmp(
                          date = @today,
                          time = c~arrival_time,
                          tzone = t~timzone )      
                                            )                            
      INTO TABLE @connections_buffer.

  6. Finally, provide an alias name for the complete expression (suggested name: duration)

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      123456789101112131415161718192021222324252627
      
  DATA(today) = cl_abap_context_info=>get_system_date( ).
   
  SELECT
    FROM /lrn/connection AS c
    LEFT OUTER JOIN /lrn/airport AS f
      ON c~airport_from_id = f~airport_id
    LEFT OUTER JOIN /lrn/airport AS t
      ON c~airport_to_id = t~airport_id
  FIELDS carrier_id, connection_id,
         airport_from_id, airport_to_id, departure_time, arrival_time,
         f~timzone AS timezone_from,
         t~timzone AS timezone_to,
         div( 
             tstmp_seconds_between(
               tstmp1 = dats_tims_to_tstmp(
                          date = @today,
                          time = c~departure_time,
                          tzone = f~timzone ),
               tstmp2 = dats_tims_to_tstmp(
                          date = @today,
                          time = c~arrival_time,
                          tzone = t~timzone )      
                                            )
                , 60 )                             
      INTO TABLE @connections_buffer.

  7. Now that you successfully moved the calculation into the SELECT statement, remove or comment the complete loop over the connections_buffer attribute.

    1. Mark the LOOP statement, the ENDLOOP statement all code rows between them and press Ctrl + < to add a comment sign in front of each code row.

Task 3: Convert Currencies

In local class LCL_PASSENGER_FLIGHT, adjust the implementation of methods CONSTRUCTOR and GET_FLIGHTS_BY_CARRIER. Move the currency conversion into the field list of the SELECT statements.

Steps

  1. In local class LCL_PASSENGER_FLIGHT, navigate to the implementation of the CONSTRUCTOR method.

    1. Perform this step as before.

  2. Locate the method call that converts the flight price to the currency code that is stored in attribute currency. Analyze it, then add comment signs at the beginning of the code rows.

    1. The currency conversion is done by calling method convert_to_local_currency of global class cl_exchange_rates.

    2. To comment the call of the method, mark the TRY statement, the ENDTRY statement, and all code rows between them and press Ctrl + <.

  3. Scroll up to the SELECT SINGLE statement. In the field list, replace table field PRICE with a call of the SQL function for currency conversions.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      123456789101112
      
 SELECT SINGLE
   FROM /lrn/passflight
 FIELDS plane_type_id,
        seats_max, seats_occupied,
        seats_max - seats_occupied AS seats_free,
        currency_code
        WHERE carrier_id    = @i_carrier_id
         AND connection_id = @i_connection_id
         AND flight_date   = @i_flight_date
        INTO CORRESPONDING FIELDS OF @flight_raw .

  4. Supply the four mandatory parameters of the SQL function. As conversion rate date, use the flight date (table field flight_date), as source amount and source currency, use table fields price and currency, and as target currency, use static attribute currency.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      123456789101112131415
      
 SELECT SINGLE
   FROM /lrn/passflight
 FIELDS plane_type_id,
        seats_max, seats_occupied,
        seats_max - seats_occupied AS seats_free,
*        price, 
        currency_conversion(                   
                           ) AS price,
        currency_code
        WHERE carrier_id    = @i_carrier_id
         AND connection_id = @i_connection_id
         AND flight_date   = @i_flight_date
        INTO CORRESPONDING FIELDS OF @flight_raw .

  5. Supply optional parameter on_error with a suitable value to get the same behavior as before.

    Hint

    Refer to the documentation of the SQL function to find out about the possible values for the parameter and their meaning.

    1. You find the allowed values as components of structured constant c_on_error in global class SQL_CURRENCY_CONVERSION.

    2. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      12345678910111213141516171819
      
 SELECT SINGLE
   FROM /lrn/passflight
 FIELDS plane_type_id,
        seats_max, seats_occupied,
        seats_max - seats_occupied AS seats_free,
*        price, 
        currency_conversion(
          amount             = price,
          source_currency    = currency_code,
          target_currency    = @currency,
          exchange_rate_date = flight_date
                           ) AS price,
        currency_code
        WHERE carrier_id    = @i_carrier_id
         AND connection_id = @i_connection_id
         AND flight_date   = @i_flight_date
        INTO CORRESPONDING FIELDS OF @flight_raw .

  6. In the field list, replace the table field currency_code with the currency attribute.

    1. Adjusted the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      12345678910111213141516171819
      
 SELECT SINGLE
   FROM /lrn/passflight
 FIELDS plane_type_id,
        seats_max, seats_occupied,
        seats_max - seats_occupied AS seats_free,
*        price, 
        currency_conversion(
          amount             = price,
          source_currency    = currency_code,
          target_currency    = @currency,
          exchange_rate_date = flight_date, 
          on_error = @sql_currency_conversion=>c_on_error-set_to_null
                           ) AS price,
        WHERE carrier_id    = @i_carrier_id
         AND connection_id = @i_connection_id
         AND flight_date   = @i_flight_date
        INTO CORRESPONDING FIELDS OF @flight_raw .

  7. Go to the implementation of method GET_FLIGHTS_BY_CARRIER and adjust the SELECT statement there in the same way.

    1. Copy the last two elements of the field list into the clip board (price and currency_code).

    2. Go to the implementation of method GET_FLIGHTS_BY_CARRIER.

    3. In the field list of the SELECT statement, replace the last two fields (price and currency_code) with the content of the clipboard.

    4. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      123456789
      
  SELECT
    FROM /lrn/passflight
  FIELDS carrier_id, connection_id, flight_date,
         plane_type_id, seats_max, seats_occupied,
         seats_max - seats_occupied AS seats_free,
     WHERE carrier_id = @i_carrier_id
      INTO TABLE @flights_buffer.

  8. Activate and test your global class as console app.

    1. Press Ctrl + F3.

    2. Press F9.

Log in to track your progress & complete quizzes