ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Operands and Expressions →  ABAP SQL - SQL Expressions sql_exp →  sql_exp - sql_func →  ABAP SQL - Built-In Functions sql_func →  ABAP SQL - Special Functions →  ABAP SQL - Date Functions and Time Functions 

sql_exp - Date/Time Conversions

Other versions: 7.31 | 7.40 | 7.54

Syntax

... TSTMP_TO_DATS( tstmp    = tstmp, 
    
              tzone    = tzone[, 

                   client   = client][, 

                   on_error = on_error] ) 

  | TSTMP_TO_TIMS( tstmp    = tstmp, 
   
               tzone    = tzone[, 

                   client   = client][, 

                   on_error = on_error] ) 

  | TSTMP_TO_DST(  tstmp    = tstmp, 
 
                 tzone    = tzone[, 

                   client   = client][, 

                   on_error = on_error] ) 

  | DATS_TIMS_TO_TSTMP( date     = date, 
  
                     time     = time, 

                        tzone    = tzone[, 

                 
      client   = client][, 

                        on_error = on_error]  ) ... 

Variants

1. ... TSTMP_TO_DATS( ... )

2. ... TSTMP_TO_TIMS( ... )

3. ... TSTMP_TO_DST( ... )

4. ... DATS_TIMS_TO_TSTMP( ... )

Effect

These SQL functions convert time stamps into dates or times, and back again. The argument tstmp must have the built-in data type DEC with length 15, or the type of the data element TIMESTAMP. The content of an argument like this is interpreted as an ABAP-specific time stamp in a packed number.

The arguments of the functions are specified as a comma-separated list in parentheses. A blank must be placed after the opening parenthesis and before the closing parenthesis. The functions have keyword parameters. Optional parameters can be assigned actual parameters when called; non-optional parameters, however, must be assigned actual parameters.

SQL expressions, in particular individual columns, literals, SQL functions, host variables or host expressions or host expressions can be specified as actual parameters. Only enumeration constants of specific classes can be passed to the parameter on_error. If an actual parameter contains the null value, every function returns a null value.

The conversions follow the ABAP-specific rules for time zones. The associated database tables must be filled correctly.

Notes

• These functions move the functions of the ABAP statements CONVERT TIME STAMP and CONVERT INTO TIME STAMP to the database.

• When used, these functions require the strict mode from Release 7.53.

Variant 1

... TSTMP_TO_DATS( ... )

Effect

The function TSTMP_TO_DATS extracts the local date for the time zone specified in tzone from a time stamp in the argument tstmp.

The actual parameter for the formal parameter tstmp must have the built-in data type DEC with length 15 and no decimal places and contain a valid time stamp in the format YYYYMMDDHHMMSS. tzone expects an actual parameter of the type CHAR with length 6, either initial or containing a valid time zone. If an initial time zone is specified, no time shift is calculated. The actual parameter for the optional formal parameter client must have the built-in dictionary type CLNT and contain a valid client ID. The default value is the current client ID. This client ID is used in the evaluation of the system tables of the rules for time zones. The return value has the built-in data type DATS.

The optional parameter on_error defines the way errors are handled. The parameter for on_error must be an enumerated object with the enumerated type ON_ERROR from the class SQL_TSTMP_TO_DATS and the following enumeration constants can be passed:

• SQL_TSTMP_TO_DATS=>FAIL (an error raises an exception (default))

• SQL_TSTMP_TO_DATS=>SET_TO_NULL (an error returns the null value)

Variant 2

... TSTMP_TO_TIMS( ... )

Effect

The function TSTMP_TO_TIMS extracts the local time for the time zone specified in tzone from a time stamp in the argument tstmp.

The same applies to the parameters tstmp, tzone, client, and on_error as to the function TSTMP_TO_DATS. Here, the class of the enumerated type of on_error is SQL_TSTMP_TO_TIMS.

The return value has the built-in data type TIMS.

Variant 3

... TSTMP_TO_DST( ... )

Effect

The function TSTMP_TO_DST extracts the summer time marker for the time zone specified in tzone from a time stamp in the argument tstmp. This is "X" if the time stamp for the time zone is in the summer time, otherwise it is initial.

The same applies to the parameters tstmp, tzone, client, and on_error as to the function TSTMP_TO_DATS. Here, the class of the enumerated type of on_error is SQL_TSTMP_TO_DST.

The return value has the built-in data type CHAR with length 1.

Variant 4

... DATS_TIMS_TO_TSTMP( ... )

Effect

The function DATS_TIMS_TO_TSTMP constructs a time stamp from a local date specified in date and a local time specified in time in the time zone specified in tzone. The summer time is respected implicitly.

The actual parameter for the formal parameter date must have the built-in data type DATS and should contain a valid date. The actual parameter for the formal parameter time must have the built-in data type TIMS and should contain a valid time. The same applies to the parameters tzone, client, and on_error as to the function TSTMP_TO_DATS. Here, the class of the enumerated type of on_error is SQL_DATS_TIMS_TO_TSTMP.

The return value has the built-in data type DEC with length 15 and represents an ABAP-specific time stamp in a packed number.

Example

Extracts date, time, and summer time flag of the current UTC time stamp using the conversion functions TSTMP_TO_DATS, TSTMP_TO_TIMS, and TSTMP_TO_DST. The values of the columns DATS1 and TIMS1 of the database table DEMO_EXPRESSIONS are combined into a time stamp by the conversion function DATS_TIMS_TO_TSTMP. The program DEMO_SQL_DATE_TIME executes the statement and displays the result.

DELETE FROM demo_expressions. 
INSERT demo_expressions FROM @( VALUE #( id = 'X' 
                                       dats1 = sy-datum 
                                        tims1 = sy-uzeit ) ). 

DATA tzone TYPE timezone. 
CALL FUNCTION 'GET_SYSTEM_TIMEZONE' 
  IMPORTING 
    timezone = tzone. 

SELECT SINGLE 
       FROM demo_expressions 
       FIELDS 
         tstmp_current_utctimestamp( ) 
             AS tstmp, 
         tstmp_to_dats( 
           tstmp    = tstmp_current_utctimestamp( ), 
           tzone    = @tzone, 
           client   = @sy-mandt, 
          on_error = @sql_tstmp_to_dats=>set_to_null ) 
             AS dat, 
         tstmp_to_tims( 
           tstmp    = tstmp_current_utctimestamp( ), 
           tzone    = @tzone, 
           client   = @sy-mandt, 
          on_error = @sql_tstmp_to_tims=>set_to_null ) 
             AS tim, 
         tstmp_to_dst( 
           tstmp    = tstmp_current_utctimestamp( ), 
           tzone    = @tzone, 
           client   = @sy-mandt, 
          on_error = @sql_tstmp_to_dst=>set_to_null ) 
             AS dst, 
           dats_tims_to_tstmp( 
             date     = dats1, 
             time     = tims1, 
             tzone    = @tzone, 
             client   = @sy-mandt, 
             on_error = @sql_dats_tims_to_tstmp=>set_to_null ) 
               AS dat_tim 
       INTO @DATA(result).