This knowledge article may contain information that does not apply to version 21.05 or later which runs in a container environment. Please refer to
Article Number 000385088 for more information about troubleshooting BMC products in containers.
This SQL statement is provided only as a convenience for Remedy's customers. This formula is not supported by Remedy Technical Support. For advice and support on the functions used, and for troubleshooting, please consult with an Oracle database administrator, or with Oracle Technical Support.
For ORACLE Database
******************************
- This SQL statement demonstrates converting a REMEDY date/time, Request_DateTime,to an Oracle Date, NewDateTime :
example: T40
SELECT TO_CHAR(TO_DATE('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS') + (C3 / ( 60 * 60 * 24 )), 'MM/DD/YY HH24:MI:SS')
FROM T40;
- The constants are 60 seconds per minute >> 60 minutes per hour >> 24 hours per day are multiplied together and divided into the Remedy date/time to get the whole and fractional number of days that have elapsed since the epoch and add it to the epoch as an Oracle date.
Example: Working from the inside out, we are taking the date January 1, 1970 and converting it to an Oracle date value:
TO_DATE('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
- Now that we have an Oracle formatted date, we need to adjust that date by the amount of time that is in the column we are interested in:
TO_DATE('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS') + (C3 / ( 60 * 60 * 24 )),
- This will take the number of seconds and divide it by the number of seconds in a day. This results in a real number that is the number of days and the fraction of a day to add onto the date. (can add timezone stuff too)
- If you want a date field, you are done at this point. If however, you want to print the date, you must specify a format for that field to get the format and pieces you want. Without this step, the format is DD-MMM-YY:
TO_CHAR(TO_DATE('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS') + (C3 / ( 60 * 60 * 24 )), 'MM/DD/YY HH24:MI:SS')
- This step takes the date value and formats it as you specify.
NOTE: For more details, refer to the Oracle SQL Language Reference Manual for information on the TO_DATEand TO_CHAR functions.
References:
000029790 : Remedy - AR System - Server - How to convert EPOCH Date Integers in MS SQL Server database to readable date/time format