Updated March 8, 2023
Introduction to Timestamp to Date in Oracle
Oracle provides the different date and timestamp function and format to the user, we can convert timestamp to date as per user requirement, in oracle we can use alter table command or cast function for conversion purpose, oracle database handles the date format in a straightforward and simple way and it is very easy to understand and handle, but many user and PL/SQL developers have difficulty about the timestamp and date data type. The main problem with timestamp and date is that they don’t know how to store the date and timestamp. But, the casting of timestamps to date is very easy.
Syntax:
select specific function (system current timestamp) alias from specified table name;
Explanation:
- In the above syntax, we select a clause with a specified function, which means cast function that fetches the current timestamp from the system; after that, we use the alias name for the result and return from the specified table.
How to Convert Timestamp to Date in Oracle?
Given below shows how we can convert timestamps to date in oracle:
Date Data Type:
This is the one of the data types that we are generally very acquainted with when we consider addressing date and time esteems. It is used to store the values in different forms such as a month, day, year, century, hours, minutes, and seconds. It is commonly useful for addressing information for when something has occurred or ought to occur later on. The issue with the date data type is its granularity when attempting to decide a period span between two occasions when the occasions occur inside a moment of one another.
Here we will convert timestamp to date data type, but there are some problems with the date, such as we cannot determine which event occurs first and which event occurs second because of date data type so that reason we use timestamp data type to easily determine the event time and date as per requirement.
Oracle databases use DD – MON – YY date format, and this is the standard format of an oracle, and it is controlled by the NLS_DATE_FORMAT parameter. When a user needs to display the date value at that time, the oracle database first converts this date value into a specified internal format of an oracle. This casting uses the TO_CHAR function. Oracle has a default date format that is DD – MON – YY.
Oracle database uses the following data function as per the user requirement as follows:
- CURRENT_DATE: We can return the current system date and time with the current time zone by using this function.
- SYSDATE: By using this function, we can fetch the system current date.
- SYSTIMESTAMP: By using this function, we can fetch the current date and time with fractional seconds of the system.
- MM: By using this parameter, we can display the month in number format, for example, 09.
- MON: This parameter is used to return the month name, for example, APR.
- MONTH: By using this parameter, we display the full name of the month, for example, APRIL.
- DD: By using this parameter, we can display the date, for example, 12.
- DY: By using this parameter, we can display the name of the day, for example, MON.
- YYYY: By using this parameter, we display the year, for example, 2021.
- YY: By using this parameter, we display the last two digits of the year, for example, 21.
For converting timestamp to date we use cast function, this function works on the current timestamp of system, basically timestamp contain the whole details about the date that means hour, minute and second but sometimes we need only the date at that time we need casting of a timestamp to date data type. This is a very simple function, and we can directly use this in SQL statements.
Example of Timestamp to Date in Oracle
Given below is the example of Timestamp to Date in Oracle:
But, first, we need to create a new table by using the following SQL statement as follows.
Code:
create table t_d(timedate timestamp(4));
Explanation:
- In the above example, we created a new table name as t_d with timedate column and timestamp of 4 by using create table statement.
Output:
After inserting a timestamp value by using the following statement as follows.
Code:
insert into t_d select systimestamp from dual;
Explanation:
- In the above example, we use to insert into a statement to insert a timestamp into the t_d table, here we fetch the timestamp from a dual table, and this table is system generated table so we try to fetch the current timestamp and that timestamp we inserted into the t_d table as shown in the above statement.
Output:
Now we can see the timestamp by using the following statement as follows.
Code:
select * from t_d;
Explanation:
- In the above example, we use the select clause to see the timestamp that is inserted from the dual table.
Output:
Now use the alters table command to cast the date from the timestamp by using the following statement as follows.
Code:
alter table t_d modify (timedate date);
Explanation:
- In the above example, we use alter table command to cast the date from the system timestamp, as shown in the above statement.
Output:
Now finally, we can see the date that we convert from timestamp by using the following statement as follows.
Code:
select * from t_d;
Explanation:
- In the above example, we use the select clause to see to date that we cast from the timestamp.
Output:
So in this way, we can convert timestamp to date in oracle we can also cast function for conversion.
Conclusion
From the above article, we have seen the basic syntax of a timestamp to date, and we have also seen different examples of a timestamp to date. From this article, we saw how and when we use the timestamp to date in oracle.
Recommended Articles
This is a guide to Timestamp to Date in Oracle. Here we discuss the introduction, how to convert timestamp to date in oracle? and example. You may also have a look at the following articles to learn more –
