Oracle date formatting and the use of extract functions

Due to business needs, these two days in learning Oracle, found in Oracle's date it will give you into a very unaccustomed format, so think about how to get it into adulthood, month, day format to display, check information, look at the document, finally found a solution, in fact, the use of the To_char method.

For example, in Oracle, a table in the date, such as 2017-05-06, the column named Time, after the query found that the return is June-May-17 This format, looking at the thief is not happy, want to turn it into a month and day this format, you can do so,

To_char (Time, ' yyyy-mm-dd ') as time  //In parentheses represents the column name in the table, and the second time indicates that the converted date column name is still time

Now that's the date after the conversion, 2017-05-06

So what does the Extract function do? Extract English means to extract, select, as the name implies, it represents the interception of a particular part from a date type, for example, selecting a year or month or day.

For example, there is a table:

　　　　　　

Now I'm going to pick all the data from the time of year 2018 in the table mytable, you can do that,

Select Title,play,time from MyTable where extract (year from time) = 2018;
Or:
Select Title,play,to_char (Time, ' yyyy-mm-dd ') as time from myTable where extract (year from time) = 2018

The results are obviously all back (here's just a demo)

Now I want to select all the data from the table MyTable for the month 5 in the time, as follows:

Select Title,play,time from myTable extract (month from time) = 5;
Or:
Select Title,play,to_char (Time, ' yyyy-mm-dd ') as time from myTable where extract (month from time) = 5

　　

From table MyTable, select all data with date 6 in time as follows:

Select Title,play,time from MyTable extract (day from time) = 6;
Or:
Slect Title,play,to_char (Time, ' yyyy-mm-dd ') as time from myTable where extract (day from time) = 6;

　　

The syntax is as follows: Extract (Year | Month| Day | Hour| Minute| second from column_name) = value

Oracle date formatting and the use of extract functions