Oracle is one of the leading relational database management systems in the world. It is widely used in various industries, including finance, healthcare, and government agencies. Oracle has various built-in functions that allow users to convert dates and times from one format to another. In this blog, we will explore the different types of timestamp conversions in Oracle with examples and explanations.
Types of Timestamp Conversion in Oracle
Converting a TIMESTAMP value to a string
Oracle provides the TO_CHAR() function to convert a TIMESTAMP value to a string. The TO_CHAR() function takes two arguments: the first argument is the TIMESTAMP value that you want to convert, and the second argument is the format in which you want the TIMESTAMP value to be displayed.
Here is an example:
1SELECT TO_CHAR(SYSTIMESTAMP, 'DD-MON-YYYY HH:MI:SS.FF') FROM dual;
This query will convert the SYSTIMESTAMP value to a string in the format of 'DD-MON-YYYY HH:MI:SS.FF', for example: '12-MAR-2023 02:30:45.123456789 AM'.
Converting a string to a TIMESTAMP value
Oracle provides the TO_TIMESTAMP() function to convert a string to a TIMESTAMP value. The TO_TIMESTAMP() function takes two arguments: the first argument is the string value that you want to convert, and the second argument is the format in which the string value is represented.
Here is an example:
1SELECT TO_TIMESTAMP('12-MAR-2023 02:30:45.123456789 AM', 'DD-MON-YYYY HH:MI:SS.FF AM') FROM dual;
This query will convert the string value '12-MAR-2023 02:30:45.123456789 AM' to a TIMESTAMP value.
Extracting a specific portion of a TIMESTAMP value
Oracle provides various functions to extract specific portions of a TIMESTAMP value, such as the year, month, day, hour, minute, and second.
Here are some examples:
1 2 3 4 5 6SELECT EXTRACT(YEAR FROM SYSTIMESTAMP) FROM dual; SELECT EXTRACT(MONTH FROM SYSTIMESTAMP) FROM dual; SELECT EXTRACT(DAY FROM SYSTIMESTAMP) FROM dual; SELECT EXTRACT(HOUR FROM SYSTIMESTAMP) FROM dual; SELECT EXTRACT(MINUTE FROM SYSTIMESTAMP) FROM dual; SELECT EXTRACT(SECOND FROM SYSTIMESTAMP) FROM dual;
The first query will extract the year portion from the SYSTIMESTAMP value. Similarly, the other queries will extract the month, day, hour, minute, and second portions of the SYSTIMESTAMP value, respectively.
Converting a TIMESTAMP value to a date
Oracle provides the CAST() function to convert a TIMESTAMP value to a date. Here is an example:
1SELECT CAST(SYSTIMESTAMP AS DATE) FROM dual;
This query will convert the SYSTIMESTAMP value to a date.
In conclusion, Oracle provides various built-in functions to convert dates and times from one format to another. The TO_CHAR() function is used to convert a TIMESTAMP value to a string, the TO_TIMESTAMP() function is used to convert a string to a TIMESTAMP value, and the EXTRACT() function is used to extract specific portions of a TIMESTAMP value. Additionally, the CAST() function is used to convert a TIMESTAMP value to a date. These functions can be very helpful in managing and manipulating date and time values in Oracle.