SAP SQL DATE FUNC

Get Example source ABAP code based on a different SAP table
  SAP Help

• DATN_DAYS_BETWEEN ABAP_OSQL_FUNC
• DATN_ADD_DAYS ABAP_OSQL_FUNC
• DATN_ADD_MONTHS ABAP_OSQL_FUNC
• DATS_IS_VALID ABAP_OSQL_FUNC
• DATS_DAYS_BETWEEN ABAP_OSQL_FUNC
• DATS_ADD_DAYS ABAP_OSQL_FUNC
• DATS_ADD_MONTHS ABAP_OSQL_FUNC
• EXTRACT_YEAR ABAP_OSQL_FUNC
• EXTRACT_MONTH ABAP_OSQL_FUNC
• EXTRACT_DAY ABAP_OSQL_FUNC
• DAYNAME ABAP_OSQL_FUNC
• MONTHNAME ABAP_OSQL_FUNC
• WEEKDAY ABAP_OSQL_FUNC
• DAYS_BETWEEN ABAP_OSQL_FUNC
• ADD_DAYS ABAP_OSQL_FUNC
• ADD_MONTHS ABAP_OSQL_FUNC

ABAP_SQLFUNC - Date Functions

ABAP_SYNTAX_FORMS

Generic DateFunctions
1 ... IS_VALID( date$|time$|utclong )
$| EXTRACT_YEAR( date$|utclong )
$| EXTRACT_MONTH( date$|utclong )
$| EXTRACT_DAY( date$|utclong )
$| DAYNAME( date$|utclong )
$| MONTHNAME( date$|utclong )
$| WEEKDAY( date$|utclong )
$| DAYS_BETWEEN( ${date1$|utclong1$},${date2$|utclong2$} )
$| ADD_DAYS( ${date$|utclong$},days )
$| ADD_MONTHS( ${date$|utclong$},months ) ...

Functions forDATN
2 ... DATN_DAYS_BETWEEN( date1,date2 )
$| DATN_ADD_DAYS( date,days )
$| DATN_ADD_MONTHS( date,months ) ...

Functions forDATS
3 ... DATS_IS_VALID( date )
$| DATS_DAYS_BETWEEN( date1,date2 )
$| DATS_ADD_DAYS( date,days )
$| DATS_ADD_MONTHS( date,months ) ...

What does it do?
These SQL functions perform operations on dates with arguments of the built-in data types DATN, DATS, TIMN, TIMS, and UTCLONG. The first set covers generic functions, the second set covers a function depending on the data type DATN , and the third set covers a function depending on the 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 in front of the closing parenthesis. SQL expressions of matching data types can be passed as actual parameters. If an actual parameter contains the null value, every function except IS_VALID and DATS_IS_VALID returns a null value.

Generic Time Functions

ABAP_SYNTAX
... IS_VALID( date$|time$|utclong )
$| EXTRACT_YEAR( date$|utclong )
$| EXTRACT_MONTH( date$|utclong )
$| EXTRACT_DAY( date$|utclong )
$| DAYNAME( date$|utclong )
$| MONTHNAME( date$|utclong )
$| WEEKDAY( date$|utclong )
$| DAYS_BETWEEN( ${date1$|utclong1$},${date2$|utclong2$} )
$| ADD_DAYS( ${date$|utclong$},days )
$| ADD_MONTHS( ${date$|utclong$},months ) ...

ABAP_VARIANTS:
1 ... IS_VALID( date$|time$|utclong )
2 ... EXTRACT_YEAR( date$|utclong )
3 ... EXTRACT_MONTH( date$|utclong )
4 ... EXTRACT_DAY( date$|utclong )
5 ... DAYNAME( date$|utclong )
6 ... MONTHNAME( date$|utclong )
7 ... WEEKDAY( date$|utclong )
8 ... DAYS_BETWEEN( ${date1$|utclong1$},${date2$|utclong2$} )
9 ... ADD_DAYS( ${date$|utclong$},days )
10 ... ADD_MONTHS( ${date$|utclong$},months )

What does it do?
These SQL functions perform operations with arguments of the built-in data types DATN, DATS, TIMN, TIMS, and UTCLONG.



Latest notes:
NON_V5_HINTS
All generic functions enforce ABAP_STRICT_783 strict mode from ABAP_RELEASE ABAP_783 / .
ABAP_HINT_END

ABAP_VARIANT_1 ... IS_VALID( date$|time$|utclong ) ...

What does it do?
The generic function IS_VALID determines whether the specification date, time, or utclong has a valid format. For more information about the IS_VALID function, see time functions.



Latest notes:
The generic function IS_VALID applies to time functions and time stamp functions as well.
NON_V5_HINTS
ABAP_HINT_END

ABAP_EXAMPLE_VX5
Applying the generic function to a date column of the DDIC database table DEMO_EXPRESSIONS.
ABEXA 01661
ABAP_EXAMPLE_END

ABAP_VARIANT_2 ... EXTRACT_YEAR( date$|utclong ) ...

What does it do?
The generic function EXTRACT_YEAR extracts the year of a date or a time stamp. The actual parameter must have the built-in data type DATN, DATS, or UTCLONG.
The result of the function EXTRACT_YEAR has the data type INT4. The function returns the value 1 for initial input values. If no valid date is passed as a DATS value, the function EXTRACT_YEAR raises a catchable exception of class CX_SY_OPEN_SQL_DB.



Latest notes:

If the built-in data type DATN or UTCLONG is used, the function EXTRACT_YEAR calls the HANA function EXTRACT.

If the built-in data type DATS is used, the function EXTRACT_YEAR internally uses the ABAP SQL SUBSTRING function and a CAST expression afterwards. The corresponding rules apply.
NON_V5_HINTS
ABAP_HINT_END

ABAP_VARIANT_3 ... EXTRACT_MONTH( date$|utclong ) ...

What does it do?
The generic function EXTRACT_MONTH extracts the month of a date or a time stamp. The actual parameter must have the built-in data type DATN, DATS, or UTCLONG.
The result of the function EXTRACT_MONTH has the data type INT4. The function returns the value 1 for initial input values. If no valid date is passed as a DATS value, the function EXTRACT_MONTH raises a catchable exception of class CX_SY_OPEN_SQL_DB.



Latest notes:

If the built-in data type DATN or UTCLONG is used, the function EXTRACT_MONTH calls the HANA function EXTRACT.

If the built-in data type DATS is used, the function EXTRACT_MONTH internally uses the ABAP SQL SUBSTRING function and a CAST expression afterwards. The corresponding rules apply.
NON_V5_HINTS
ABAP_HINT_END

ABAP_VARIANT_4 ... EXTRACT_DAY( date$|utclong ) ...

What does it do?
The generic function EXTRACT_DAY extracts the day of a date or a time stamp. The actual parameter must have the built-in data type DATN, DATS, or UTCLONG.
The result of the function EXTRACT_DAY has the data type INT4. The function returns the value 1 for initial input values. If no valid date is passed as a DATS value, the function EXTRACT_DAY raises a catchable exception of class CX_SY_OPEN_SQL_DB.



Latest notes:

If the built-in data type DATN or UTCLONG is used, the function EXTRACT_DAY calls the HANA function EXTRACT.

If the built-in data type DATS is used, the function EXTRACT_DAY internally uses the ABAP SQL SUBSTRING function and a CAST expression afterwards. The corresponding rules apply.
NON_V5_HINTS
ABAP_HINT_END

ABAP_VARIANT_5 ... DAYNAME( date$|utclong ) ...

What does it do?
The generic function DAYNAME returns the name of a day of a date or a time stamp in uppercase letters in English. The actual parameter must have the built-in data type DATN , DATS, or UTCLONG.
The result of the function DAYNAME has the data type CHAR with length 9. If no valid date is saved in a DATS value, the function DAYNAME raises a catchable exception of class CX_SY_OPEN_SQL_DB.

ABAP_VARIANT_6 ... MONTHNAME( date$|utclong ) ...

What does it do?
The generic function MONTHNAME returns the name of a month of a date or a time stamp in uppercase letters in English. The actual parameter must have the built-in data type DATN , DATS, or UTCLONG.
The result of the function MONTHNAME has the data type CHAR with length 9. If no valid date is saved in a DATS value, the function MONTHNAME raises a catchable exception of class CX_SY_OPEN_SQL_DB.

ABAP_VARIANT_7 ... WEEKDAY( date$|utclong ) ...

What does it do?
The generic function WEEKDAY returns the number of a weekday of a date or a time stamp from 0 to 6. The actual parameter must have the built-in data type DATN, DATS, or UTCLONG.
The result of the function WEEKDAY has the data type INT4. If no valid date is saved in a DATS value, the function WEEKDAY raises a catchable exception of class CX_SY_OPEN_SQL_DB.

ABAP_VARIANT_8 ... DAYS_BETWEEN( ${date1$|utclong1$} ,${date2$|utclong2$} ) ...

What does it do?
The function DAYS_BETWEEN calculates the difference between two dates date1 or utclong1 and date2 or utclong2 in days. The actual parameters must have the built-in data type DATN, DATS, or UTCLONG and must contain a valid date in the format YYYYMMDD or a valid time stamp in a format like 0001-01-01T00:00:00.0000000. Any invalid date is initialized and set to the value 00010101 before the calculation. Any invalid time stamp is initialized and set to the value 0001-01-01T00:00:00.0000000 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.



Latest notes:
It is possible to use two different data types for the function DAYS_BETWEEN and to calculate the days between these two dates.
NON_V5_HINTS
ABAP_HINT_END

ABAP_VARIANT_9 ... ADD_DAYS( ${date$|utclong$},days ) ...

What does it do?
The function ADD_DAYS adds days days to a specified date date or time stamp utclong.

The actual parameter date must have the built-in data type DATN or DATS and must contain a valid date in the format YYYYMMDD. The actual parameter utclong must have the built-in data type UTCLONG and must contain a valid time stamp in a format like 0001-01-01T00:00:00.0000000. Any invalid date is initialized and set to the value 00010101 before the calculation and any invalid time stamp is initialized and set to the value 0001-01-01T00:00:00.0000000 before the calculation.

The actual parameter days must have the built-in data type INT4.
The result has the data type of the first actual parameter days or utclong. If days is positive, the number of days is added to date or utclong. In other cases, the number of days is subtracted. If the calculation produces an invalid date, the function raises a catchable exception of class CX_SY_OPEN_SQL_DB if the actual parameter days is of data type DATN or UTCLONG. If the actual parameter days is of data type DATS, the function returns the initial date.

ABAP_VARIANT_10 ...ADD_MONTHS( ${date$|utclong$},months ) ...

What does it do?
The function ADD_MONTHS adds months months to a specified date date or time stamp utclong.

The actual parameter date must have the built-in data type DATN or DATS and must contain a valid date in the format YYYYMMDD. The actual parameter utclong must have the built-in data type UTCLONG and must contain a valid time stamp in a format like 0001-01-01T00:00:00.0000000. Any invalid date is initialized and set to the value 00010101 before the calculation and any invalid time stamp is initialized and set to the value 0001-01-01T00:00:00.0000000 before the calculation.

The actual parameter months must have the built-in data type INT4.
The result has the data type of the first actual parameter days or utclong. If months is positive, the number of months is added to date or utclong. In other cases, the number of months is subtracted.
The system tries to create a date with the same day in an earlier or later month. If the target month has fewer days than the source month, the last day of the target month is returned. If the calculation produces an invalid date, the function raises a catchable exception of class CX_SY_OPEN_SQL_DB if the actual parameter months is of data type DATN or UTCLONG. If the actual parameter months is of data type DATS, the function returns the initial date.

ABAP_EXAMPLE_VX5
Applying the generic functions to date and time stamp columns of the DDIC database table DEMO_EXPRESSIONS.
ABEXA 01660
ABAP_EXAMPLE_END

Functions for DATN

ABAP_SYNTAX
... DATN_DAYS_BETWEEN( date1,date2 )
$| DATN_ADD_DAYS( date,days )
$| DATN_ADD_MONTHS( date,months ) ...

ABAP_VARIANTS:
1 ... DATN_DAYS_BETWEEN( date1,date2 )
2 ... DATN_ADD_DAYS( date,days )
3 ... DATN_ADD_MONTHS( date,months )

What does it do?
These SQL functions perform operations on dates with arguments of the built-in data type DATN.

ABAP_VARIANT_1 ... DATN_DAYS_BETWEEN( date1,date2 )

What does it do?
The function DATN_DAYS_BETWEEN calculates the difference between two specified dates date1 and date2 in days. The actual parameters must have the built-in data type DATN and must contain a valid date in the format YYYYMMDD . The result has the data type INT4 . If date2 is greater than date1, the result is positive. Otherwise, the result is negative. If the result is outside of the range of valid dates, an error occurs.



Latest notes:
NON_V5_HINTS
DATN_DAYS_BETWEEN enforces ABAP_STRICT_778 strict mode from ABAP_RELEASE ABAP_778 / .
ABAP_HINT_END

ABAP_VARIANT_2 ... DATN_ADD_DAYS( date,days )

What does it do?
The function DATN_ADD_DAYS adds days days to a specified date date.

The actual parameter date must have the built-in data type DATN and must contain a valid date in the format YYYYMMDD.

The actual parameter days must have the built-in data type INT4.
The result has the data type DATN . 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, an error occurs.



Latest notes:
NON_V5_HINTS
DATN_ADD_DAYS enforces ABAP_STRICT_778 strict mode from ABAP_RELEASE ABAP_778 / .
ABAP_HINT_END

ABAP_VARIANT_3 ... DATN_ADD_MONTHS( date,months )

What does it do?
The function DATN_ADD_MONTHS adds months months to a specified date date.

The actual parameter date must have the built-in data type DATN and it must contain a valid date in the format YYYYMMDD.

The actual parameter months must have the built-in data type INT4.
The result has the data type DATN . If months is positive, the number of months is added to date. In other cases, the number of months is subtracted.
The system tries to create a date with the same day in an earlier or later month. If the target month has fewer days than the source month, the last day of the target month is returned. If the calculation produces an invalid date, an error occurs.



Latest notes:
NON_V5_HINTS
DATN_ADD_MONTHS enforces ABAP_STRICT_778 strict mode from ABAP_RELEASE ABAP_778 / .
ABAP_HINT_END

Functions for DATS

ABAP_SYNTAX
... DATS_IS_VALID( date )
$| DATS_DAYS_BETWEEN( date1,date2 )
$| DATS_ADD_DAYS( date,days )
$| DATS_ADD_MONTHS( date,months ) ...

ABAP_VARIANTS:
1 ... DATS_IS_VALID( date )
2 ... DATS_DAYS_BETWEEN( date1,date2 )
3 ... DATS_ADD_DAYS( date,days )
4 ... DATS_ADD_MONTHS( date,months )

What does it do?
These SQL functions perform operations on dates with arguments of the built-in data type DATS.

ABAP_VARIANT_1 ... DATS_IS_VALID( date )

What does it do?
The function DATS_IS_VALID determines whether date 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.



Latest notes:
The value 00010101 is a valid date here but the value 00000000 is not.
NON_V5_HINTS
ABAP_HINT_END

ABAP_VARIANT_2 ... DATS_DAYS_BETWEEN( date1,date2 )

What does it do?
The function DATS_DAYS_BETWEEN calculates the difference between two 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 date is initialized and 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.



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

ABAP_VARIANT_3 ... DATS_ADD_DAYS( date,days )

What does it do?
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 is initialized and 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.



Latest notes:
For the calculation, the specified date is converted to an integer like in ABAP, and the result is converted to a date again while the corresponding rules apply.
NON_V5_HINTS
ABAP_HINT_END

ABAP_VARIANT_4 ... DATS_ADD_MONTHS( date,months )

What does it do?
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 is initialized and 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.
The system tries to create a date with the same day in an earlier or later month. If the target month has fewer days than the source month, the last day of the target month is returned. If the calculation produces an invalid date, an error occurs.

ABAP_EXAMPLE_VX5
Applying the date functions to date columns of the DDIC database table DEMO_EXPRESSIONS. The class CL_DEMO_SQL_DATE_FUNCTIONS executes this access to the table and displays the result.
ABEXA 01269
ABAP_EXAMPLE_END