Ansicht
Dokumentation

ABENSQL_DATE_FUNC - SQL DATE FUNC

Fill RESBD Structure from EBP Component Structure Vendor Master (General Section)
This documentation is copyright by SAP AG.

- Date Functions

Generic date functions

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 for DATN

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

Functions for DATS

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

Effect

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

... 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 ) ...

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 )

Effect

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

Note

All generic functions enforce strict mode from Release .

Variant 1

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

Effect

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.

Note

The generic function IS_VALID applies to time functions and time stamp functions as well.

Example

Applying the generic function to a date column of the DDIC database table DEMO_EXPRESSIONS.

Variant 2

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

Effect

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.

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.

Variant 3

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

Effect

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.

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.

Variant 4

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

Effect

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.

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.

Variant 5

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

Effect

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.

Variant 6

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

Effect

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.

Variant 7

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

Effect

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.

Variant 8

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

Effect

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.

Note

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

Variant 9

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

Effect

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.

Variant 10

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

Effect

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.

Example

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

Functions for DATN

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

Variants:

1. ... DATN_DAYS_BETWEEN( date1,date2 )

2. ... DATN_ADD_DAYS( date,days )

3. ... DATN_ADD_MONTHS( date,months )

Effect

These SQL functions perform operations on dates with arguments of the built-in data type DATN.

Variant 1

... DATN_DAYS_BETWEEN( date1,date2 )

Effect

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.

Note

DATN_DAYS_BETWEEN enforces strict mode from Release .

Variant 2

... DATN_ADD_DAYS( date,days )

Effect

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.

Note

DATN_ADD_DAYS enforces strict mode from Release .

Variant 3

... DATN_ADD_MONTHS( date,months )

Effect

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.

Note

DATN_ADD_MONTHS enforces strict mode from Release .

Functions for DATS

... 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 on dates with arguments of the built-in data type DATS.

Variant 1

... DATS_IS_VALID( date )

Effect

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.

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

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

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 the corresponding rules apply.

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

Example

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

General Material Data ROGBILLS - Synchronize billing plans
This documentation is copyright by SAP AG.

Length: 31877 Date: 20240419 Time: 115647 sap01-206 ( 384 ms )

Ansicht Dokumentation

ABENSQL_DATE_FUNC - SQL DATE FUNC

- Date Functions

Effect

Generic Time Functions

Variants:

Effect

Note

Variant 1

Effect

Note

Example

Variant 2

Effect

Notes

Variant 3

Effect

Notes

Variant 4

Effect

Notes

Variant 5

Effect

Variant 6

Effect

Variant 7

Effect

Variant 8

Effect

Note

Variant 9

Effect

Variant 10

Effect

Example

Functions for DATN

Variants:

Effect

Variant 1

Effect

Note

Variant 2

Effect

Note

Variant 3

Effect

Note

Functions for DATS

Variants:

Effect

Variant 1

Effect

Note

Variant 2

Effect

Note

Variant 3

Effect

Note

Variant 4

Effect

Example

Ansicht
Dokumentation