Finding Number of Days Between Two Dates

In this post, we’ll cover how to find the number of days between two dates, using %DATEDIFF. This can be placed in a Query Expression.

Some users will use expressions that are specific to the database, such as Oracle DB or MS SQL Server DB. Instead, use MetaSQL whenever possible.

To find the difference in days between today and a date, use the following:

%DateDiff(%datein(A.REQ_DT),%CurrentDateIn)

To find the difference between two dates, when you don’t need today, use the following:

%DateDiff(%datein(A.DATE1),%datein(A.DATE2))

You have to use %datein to format the date correctly. PeopleSoft by default turns your date into a string; %datein will turn it back into a date.

Listing of Date Math MetaSQL we find most useful:

%DateAdd(from_date, nbr_days_to_add) – You can use negative numbers in the second parameter.
%DateDiff(from_date, to_date)
%DateTimeDiff(from_datetime, to_datetime) – Gives the difference between two date/times in minutes.