Date and Time in Meta-SQL

The Meta-SQL date/time constructs you should know about are:

For input/output of the current date/time:

  • %CurrentDateIn
  • %CurrentDateOut
  • %CurrentDateTimeIn
  • %CurrentDateTimeOut
  • %CurrentTimeIn
  • %CurrentTimeOut

For input/output of any date/time:

  • %DateIn
  • %DateOut
  • %DateTimeIn
  • %DateTimeOut
  • %TimeIn
  • %TimeOut

Remember that:

  • input date/time goes in the WHERE clause (conditions)
  • output date/time goes in the SELECT clause (returned data)
NOTE: with PeopleCode if you are using a date that is returned from SQL and storing it into a date variable, make sure you wrap your date with %DateOut in your SQL, otherwise PeopleSoft will throw an invalid date error.

For input/output of a null date/time:

  • %DateNull
  • %DateTimeNull
  • %TimeNull

For getting part of a date/time:

  • %DatePart gets the date
  • %TimePart gets the time

For date arithmetic:

  • %DateAdd(from, days_to_add) gives the resulting date. You can use negative numbers to get a past date. So this is also the DateSubtract function.
  • %DateDiff(from, to) gives the difference between two dates in days
  • %DateTimeDiff(from, to) gives the difference between two date/times in minutes
No Comments
Back to top