Skip to content

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 Functions

Other versions: 7.31 | 7.40 | 7.54

Syntax



... DATS_IS_VALID( date ) 
  | DATS_DAYS_BETWEEN( date1,date2 ) 

  | DATS_ADD_DAYS( date,days ) 
  | DATS_ADD_MONTHS( date,months ) ...

Variants

1. ... DATS_IS_VALID( date )

2. ... DATS_DAYS_BETWEEN( date1,date2 )

3. ... DATS_ADD_DAYS( date,days )

4. ... DATS_ADD_MONTHS( date,months )

Effect

These SQL functions perform operations with arguments of the built-in data type DATS. 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. SQL expressions of matching data types can be specified as actual parameters. If an actual parameter contains the null value, every function except DATS_IS_VALID returns a null value.

Variant 1

... DATS_IS_VALID( date )

Effect

The function DATS_IS_VALID determines whether date (if specified) contains a valid date in the format YYYYMMDD. The actual parameter must have the built-in data type DATS. The result has the data type INT4. A valid date produces the value 1 and all other input values (including the null value) produce the value 0.


Note

The value "00010101" is a valid date here but the value "00000000" is not.

Variant 2

... DATS_DAYS_BETWEEN( date1,date2 )

Effect

The function DATS_DAYS_BETWEEN calculates the difference between two specified dates, date1 and date2, in days. The actual parameters must have the built-in data type DATS and should contain a valid date in the format YYYYMMDD. Any invalid dates specified are initialized or set to the value "00010101" before the calculation. The result has the data type INT4. If date2 is greater than date1, the result is positive. In the reverse case, it is negative.


Note

Before the difference is calculated, the specified dates are converted to integers, like in ABAP, and the corresponding rules apply.

Variant 3

... DATS_ADD_DAYS( date,days )

Effect

The function DATS_ADD_DAYS adds days days to a specified date date.

  • The actual parameter date must have the built-in data type DATS and should contain a valid date in the format YYYYMMDD. Any invalid date specified is initialized or set to the value "00010101" before the calculation.
  • The actual parameter days must have the built-in data type INT4.

The result has the data type DATS. If days is positive, the number of days is added to date. In other cases, the number of days is subtracted. If the calculation produces an invalid date, the initial value is reset.


Note

For the calculation, the specified date is converted to an integer, like in ABAP, and the result is converted to a date again while applying the corresponding rules.

Variant 4

... DATS_ADD_MONTHS( date,months )

Effect

The function DATS_ADD_MONTHS adds months months to a specified date date.

  • The actual parameter date must have the built-in data type DATS and should contain a valid date in the format YYYYMMDD. Any invalid date specified is initialized or set to the value "00010101" before the calculation.
  • The actual parameter months must have the built-in data type INT4.

The result has the data type DATS. If months is positive, the number of months is added to date. In other cases, the number of months is subtracted.

An attempt is made to create a date with the same day in an earlier or later month. If the maximum day possible in a month is exceeded, the greatest possible day is used. If the calculation produces an otherwise invalid date, the initial value is reset.


Example

Applying the date functions to date columns of the database table DEMO_EXPRESSIONS. The program DEMO_SQL_DATE_FUNCTIONS executes this access to the table and represents the result.

DELETE FROM demo_expressions. 
INSERT demo_expressions FROM @( 
  VALUE #( id = 'X' dats1 = sy-datum dats2 = sy-datum + 100 ) ). 

SELECT SINGLE dats1, dats2, 
              dats_is_valid( dats1 ) AS valid, 
              dats_days_between( dats1, dats2 ) AS days_between, 
              dats_add_days( dats1,100 ) AS add_days, 
              dats_add_months( dats1,-1 ) AS add_month 
       FROM demo_expressions 
       INTO @DATA(result).