ABAP - Keyword Documentation →  ABAP - Programming Language →  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 →  sql_func - Special Functions →  sql_func - Date Functions and Time Functions → 
Mail Feedback

sql_func - Date/Time Conversions

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] )
  | TSTMPL_TO_UTCL( tstmpl = tstmpl[,
                    on_error = on_error],[
                    on_initial = on_initial] )
  | TSTMPL_FROM_UTCL( utcl = utcl[,
                      on_null = on_null] )
  | DATS_TO_DATN( dats = dats[,
                  on_error = on_error],[
                  on_initial = on_initial] )
  | DATS_FROM_DATN( datn = datn[,
                    on_null = on_null] )
  | TIMS_TO_TIMN( tims = tims[,
                  on_error = on_error] )
  | TIMS_FROM_TIMN( timn = timn[,
                    on_null = on_null] )...


Variants:

1. ... TSTMP_TO_DATS( ... )

2. ... TSTMP_TO_TIMS( ... )

3. ... TSTMP_TO_DST( ... )

4. ... DATS_TIMS_TO_TSTMP( ... )

5. ... TSTMPL_TO_UTCL( ... )

6. ... TSTMPL_FROM_UTCL( ... )

7. ... DATS_TO_DATN( ... )

8. ... DATS_FROM_DATN( ... )

9. ... TIMS_TO_TIMN( ... )

10. ... TIMS_FROM_TIMN( ... )

Effect

The SQL functions described in variants 1 to 4 convert time stamps into dates or times, and vice versa. The SQL functions described in variants 5 to 10 convert time stamps, dates, and times between different types.

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

SQL expressions, in particular individual columns, type-compliant literals, SQL functions, host variables or host expressions can be specified as actual parameters.

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

Hints



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 affects how 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 enumerated constants can be passed:



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 daylight saving 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 daylight saving 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 daylight saving 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.

Variant 5  

... TSTMPL_TO_UTCL( ... )


Effect

The function TSTMPL_TO_UTCL converts a time stamp tstmpl from the ABAP Dictionary type TIMESTAMPL to the built-in dictionary type UTCLONG.

The actual parameter for the formal parameter tstmpl must have the built-in data type DEC with length 21 and 7 decimal places and contain a valid time stamp in the format YYYYMMDDHHMMSSMMMUUUN.

The optional parameter on_error affects how errors are handled:

The parameter for on_error must be an enumerated object with the enumerated type ON_ERROR from the class SQL_TSTMPL_TO_UTCL and the following enumerated constants can be passed:

The optional parameter on_initial affects how arguments with initial values are handled:

The parameter for on_initial must be an enumerated object with the enumerated type ON_INITIAL from the class SQL_TSTMPL_FROM_UTCL and the following enumerated constants can be passed:



Variant 6  

... TSTMPL_FROM_UTCL( ... )


Effect

The function TSTMPL_FROM_UTCL converts a time stamp utcl from the built-in dictionary type UTCLONG to the ABAP Dictionary type TIMESTAMPL. It is the counterpart to variant 5.

The actual parameter for the formal parameter utcl must have the built-in data type UTCLONG and should contain a valid time stamp.

The optional parameter on_null affects how null values as arguments are handled. The parameter for on_null must be an enumerated object with the enumerated type ON_NULL from the class SQL_TSTMPL_FROM_UTCL and the following enumerated constants can be passed:



Variant 7  

... DATS_TO_DATN( ... )


Effect

The function DATS_TO_DATN converts a date dats from the built-in ABAP Dictionary data type DATS to the built-in ABAP Dictionary type DATN.

The actual parameter for the formal parameter dats must have the built-in data type DATS and should contain a valid date in the format YYYYMMDD. The same applies to the actual parameters on_error and on_initial as to the function TSTMPL_TO_UTCL (Variant 5, see above). Here, the class of the enumerated type of on_error and on_initial is SQL_DATS_TO_DATN.

Hint

The same effect of the function DATS_TO_DATN can be achieved by casting.

Variant 8  

... DATS_FROM_DATN( ... )


Effect

The function DATS_FROM_DATN converts a date date from the built-in ABAP Dictionary data type DATN to the built-in ABAP Dictionary type DATS. It is the counterpart to Variant 7.

The actual parameter for the formal parameter date must have the built-in data type DATN and must contain a valid date in the format YYYYMMDD. The same applies to the actual parameter on_null, as to the function TSTMPL_FROM_UTCL (Variant 6, see above). Here, the class of the enumerated type of on_null is SQL_DATS_FROM_DATN.

Hint

The same effect of the function DATS_FROM_DATN can be achieved by casting.

Variant 9  

... TIMS_TO_TIMN( ... )


Effect

The function TIMS_TO_TIMN converts a time tims from the ABAP Dictionary type TIMS to the ABAP Dictionary type TIMN.

The actual parameter for the formal parameter tims must have the built-in data type TIMS and contain a valid time in the format HHMMSS. The same applies to the actual parameter on_error as to the function TSTMP_TO_DATS (Variant 1, see above). Here, the class of the enumerated type of on_error is SQL_TIMS_TO_TIMN.

Hint

The same effect of the function TIMS_TO_TIMN can be achieved by casting.

Variant 10  

... TIMS_FROM_TIMN( ... )


Effect

The function TIMS_FROM_TIMN converts a time time from the ABAP Dictionary type TIMN to the ABAP Dictionary type TIMS. It is the counterpart to variant 9.

The actual parameter for the formal parameter time must have the built-in data type TIMN and contain a valid time in the format HHMMSS. The same applies to the actual parameter on_null as to the function TSTMPL_FROM_UTCL (Variant 6, see above). Here, the class of the enumerated type of on_null is SQL_TIMS_FROM_TIMN.

Hint

The same effect of the function TIMS_FROM_TIMN can be achieved by casting.

Example

Extracts date, time, and daylight saving 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 DDIC database table DEMO_EXPRESSIONS are combined into a time stamp by the conversion function DATS_TIMS_TO_TSTMP. The conversion functions TSTMPL_TO_UTCL and TSTMPL_FROM_UTCL convert time stamps between TIMESTAMPL and utclong format. The conversion functions DATS_TO_DATN and DATS_FROM_DATN convert dates between DATS and DATN. The conversion functions TIMS_TO_TIMN and TIMS_FROM_TIMN convert times between TIMS and TIMN. The class CL_DEMO_SQL_DATE_TIME executes the statement and displays the result.

DATA ts TYPE timestampl.
GET TIME STAMP FIELD ts.

FINAL(utc) = utclong_current( ).

DELETE FROM demo_expressions.
INSERT demo_expressions FROM @(
  VALUE #( id = 'X'
           dats1 = cl_demo_date_time=>get_user_date( )
           tims1 = cl_demo_date_time=>get_user_time( )
           timestampl1 = ts
           utcl1 = utclong_current( ) ) ).

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,
         tstmpl_to_utcl(
           tstmpl    = timestampl1,
           on_error = @sql_tstmpl_to_utcl=>c_on_error-set_to_null,
           on_initial =
           @sql_tstmpl_to_utcl=>c_on_initial-set_to_initial )
             AS utcl,
         tstmpl_from_utcl(
           utcl = utcl1,
           on_null = @sql_tstmpl_from_utcl=>c_on_null-set_to_null )
             AS from_utcl
       INTO @FINAL(result).