Not able to get the difference between two dates (SAP)

Calculating the difference between two dates is a very basic operation that you do in database systems. In SAP environments, you can try using the DATEADD function available and use the date overload of the function. You need to pass one date as negative so that the actual difference is calculated.

Using DATEADD Function

The DATEADD function allows you to add or subtract a specified time interval from a date. By passing a negative value for one of the dates, you effectively calculate the difference between two dates.

Example

Here's how you can calculate the difference between two dates using the DATEADD function ?

SELECT DATEADD(d,-[dateTwo], [dateOne]) AS 'Difference in Dates' FROM [TABLE]

Here 'd' refers to the day unit, which means the difference will be calculated in days. The function subtracts dateTwo from dateOne by making dateTwo negative.

Alternative Date Units

You can also use other time units instead of 'd' for different granularities ?

-- Difference in months
SELECT DATEADD(m,-[dateTwo], [dateOne]) AS 'Difference in Months' FROM [TABLE]

-- Difference in years  
SELECT DATEADD(y,-[dateTwo], [dateOne]) AS 'Difference in Years' FROM [TABLE]

Where m represents months and y represents years.

Conclusion

Using the DATEADD function with negative values provides a simple way to calculate date differences in SAP database operations. This approach works effectively for various time units including days, months, and years.