How to Convert Date Format using Peoplecode in Peoplesoft
How to Convert Date Format using Peoplecode in Peoplesoft by function is DateTimeToLocalizedString of peoplesoft
&StartDT = DateTimeToLocalizedString(DERIVED_TT.KTB_START_DT, “yyyyMMdd”);
&DT= DateTimeToLocalizedString(%date, “dd-MM-yyyy”);
For example on how to apply the above function.
%date in as 2018-09-07 out as 20180907:
&date = DateTimeToLocalizedString(%Date, “yyyyMMdd”);
%Datetime in as 2018-09-07.15.41.000000 out as 20180907-154109:
&datetime = DateTimeToLocalizedString(%Datetime, “yyyyMMdd-HHmmss”);
Date and Time in Meta-SQL
For input or output of the current date or time:
- %CurrentDateIn
The %CurrentDateIn meta-SQL variable expands to a platform-specific SQL substring representing the current date in the Where clause of a SQL Select or Update statement, or when the current date is passed in an Insert statement.
- %CurrentDateOut
The %CurrentDateOut meta-SQL variable expands to platform-specific SQL for the current date in the Select clause of a SQL query.
- %CurrentDateTimeIn
The %CurrentDateTimeIn meta-SQL variable expands to a platform-specific SQL substring representing the current datetime in the Where clause of a SQL Select or Update statement, or when the current date time is passed in an Insert statement.
- %CurrentDateTimeOut
The %CurrentDateTimeOut meta-SQL variable expands to platform-specific SQL for the current datetime in the Select clause of a SQL query.
- %CurrentTimeIn
The %CurrentTimeIn meta-SQL variable expands to a platform-specific SQL substring representing the current time in the Where clause of a SQL Select or Update statement, or when the current time is passed in an Insert statement.
- %CurrentTimeOut
The %CurrentTimeOut meta-SQL variable expands to platform-specific SQL for the current time in the Select clause of a SQL query.
For input or output of any date or time:
- %DateIn
The %DateIn meta-SQL variable expands into platform-specific SQL syntax for the date. Use %DateIn whenever a date literal or Date bind variable is used in a comparison in the Where clause of a Select or Update statement, or when a Date value is passed in an Insert statement.
Restrictions Using COBOL
You can only use string literals when using this construct in COBOL. You cannot use it with bind parameters in COBOL. For example, the following works in COBOL:
|
1 |
UPDATE PS_PERSONAL_DATA SET LASTUPDT = %DATEIN('2018-09-07') |
The following SQL fails:
|
1 |
UPDATE PS_PERSONAL_DATA SET LASTUPDT = %DATEIN(:1) |
- %DateOut
The %DateOut meta-SQL variable expands to either a platform-specific SQL substring or datetime value, depending on the database platform, representing a datetime column in the Select clause of a SQL query
- %DateTimeIn
The %DateTimeIn meta-SQL variable expands to platform-specific SQL for a DateTime value in the Where clause of a SQL Select or Update statement, or when a DateTime value is passed in an Insert statement.
- %DateTimeOut
Restrictions Using COBOL
You can only use string literals when using this construct in COBOL. You cannot use it with bind parameters in COBOL. For example, the following works in COBOL:
|
1 |
UPDATE PS_PERSONAL_DATA SET LASTUPDTTM = %DATETIMEIN('2002-12-11-11.59.00.000000') |
The following SQL fails:
|
1 |
UPDATE PS_PERSONAL_DATA SET LASTUPDTTM = %DATETIMEIN(:1) |
- %TimeIn
%TimeIn expands to platform-specific SQL for a Time value in the Where clause of a SQL Select or Update statement, or when a time value is passed in an Insert statement.
Restrictions Using COBOL
You can only use string literals when using this construct in COBOL. You cannot use it with bind parameters in COBOL. For example, the following works in COBOL:
|
1 |
UPDATE PS_PERSONAL_DATA SET LASTUPTM = %TIMEIN('11:59:00:000000') |
The following SQL fails:
|
1 |
UPDATE PS_PERSONAL_DATA SET LASTUPTM = %TIMEIN(:1) |
- %TimeOut
When input date/time goes in the WHERE clause (conditions) and output date/time goes in the SELECT clause (returned data).
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(date_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.
|
1 2 |
SQLExec("Select %dateadd(%datein('2018-09-07') , 12) from PS_INSTALLATION_TR", &add); WinMessage(&add); |
- %DateDiff(date_from, date_to) gives the difference between two dates in days
|
1 2 3 4 5 6 7 |
%DateDiff(%DateIn('1997-01-01'), %DateIn("1966-06-30')) %DateDiff( date1_column, date2_column) %DateDiff ( %DateAdd(date1_column, 30), date2_column) |
The following usage is illegal (always use %Datein for inputting date literals):
|
1 |
%DateDiff('1997-01-01', '1996-06-30') (should use %DateIn for inputting date literals) |
- %DateTimeDiff(date_from, date_to) gives the difference between two date/times in minutes
The following example returns the difference in hours between the current datetime and the requested datetime:
|
1 |
%DateTimeDiff(%CurrentDateIn, RQSTDTTM) < " | RECORD.FIELDNAME * 60; |
The following example returns the difference in minutes:
|
1 |
%DateTimeDiff(%CurrentDateIn, RQSTDTTM) < " | RECORD.FIELDNAME; |
References:
http://peoplesoft.wikidot.com/date-and-time-in-meta-sqlhttps://docs.oracle.com/cd/E57990_01/pt853pbh2/eng/pt/tape/langref_UsingApplicationEngineMeta-SQL-0771d9.html#topofpage