How to query which field of the data table is the primary key in Oracle?

Oracle10g is used in the work. It is often necessary to insert the Event Date and time to a table in it. Note the date and time display and insertion modes of Oracle

Oracle 10 Gb is used in work. It is often necessary to insert the Event Date and time to a table in it. Note the date and time display and insertion modes of Oracle

Oracle 10 Gb is used in work. It is often necessary to insert the Event Date and time to a table in it. Note the date and time display and insertion modes of Oracle.
Like the built-in data types such as Number and varchar2, Oracle uses the built-in data type Date to store the Date and time. Like ms SQL Server, date and time are stored in one data type, not only for time or only for date alone for time and date data types. DATE data type storage year, month, day, hour, minute, and second.

When data of the DATE type is displayed, Oracle first needs to convert the stored value from the internal storage format to the output string. Generally, this type of conversion is done through the TO_CHAR function. If the TO_CHAR function is not used to specify a specific date and time format, the default Oracle display format is DD-MON-YY ".

For example, we have created a table x:
Create table abc (a int, B date );
Then we execute the following query statement:
Select B from abc;
We can see the following result (set head on ):
B ------------ 01-APR-09 (the language is English) or B ------------- 01-April-09 (the language is Chinese)
That is to say, Oracle will automatically call the TO_CHAR function each time a value of the DATE type is displayed, and the default DATE display format is used as the parameter. You can call the TO_CHAR function explicitly and specify the desired format. For example:

SELECT TO_CHAR (B, 'yyyy/MM/dd') AS B FROM abc;

The returned result is:

B ------------ 2010/09/01
TO_CHAR is a very powerful function. You can convert data of the Date, MLSLABEL, and Number types to VARCHAR2 in the specified format. Here we only care about the conversion of Date and time. Syntax:

TO_CHAR (d [, fmt [, 'nlsparams '])

D is a Date variable, and fmt is the Date and time format we specified. If not explicitly specified, use the default value of Oracle. Common placeholders related to date and time in fmt are as follows:

MM indicates the month in numbers (for example, 07)
Month name abbreviated to MON (for example, JUL)
Complete MONTH name (for example, JULY)
DD date (for example, 24)
Abbreviation of DY day of the week (for example, FRI)
YYYY indicates the year in four digits (for example, 2008)
YY indicates the year in two digits, and the last two digits of the year (for example, 08)
RR is similar to YY, but the two represent the year in the range of 1950 to 2049. For example, 06 is considered as 2006 rather than 1906.
AM (or PM) indicates the last afternoon
HH 12 in hexadecimal notation (1-12)
HH24 in hexadecimal notation (0-23)
MI minutes (0-59)
SS seconds (0-59)
The preceding section uses the TO_CHAR function to display data of the DATE type. The following section describes how to insert a value of the Date type into the table. In this case, we need to use the TO_DATE function to convert the Date represented by the string to the Date type.

Similar to the implicit call of the TO_CHAR function when Oracle displays the Date and time, when Oracle expects a value of the Date type, it implicitly calls the TO_DATE function to convert the input string, the default format based on is DD-MON-YY ".

Take our x table as an example. We can directly enter:

Insert into abc values (99, '31-may-08 ');
However, no matter what format is used in the display, the data we actually store will not be affected. when inserting data, simply adopting the default Oracle format is not that suitable for our work, we still need to explicitly call the TO_DATE function, for example:

Insert into abc values (99, to_date ('1970/31: 12: 00: 00AM ', 'yyyy/mm/dd: hh: mi: ssam '));
Syntax of the TO_DATE function:

TO_DATE (char [, fmt [, 'nlsparams '])

Char is a string that represents the date and time. The fmt representation is the same as that of the TO_CHAR function.

We have always mentioned that the default date and time format of Oracle is "DD-MON-YY", in fact, we can also modify this default format to change it to the format we need. Enter the following command in SQL * plus:

Alter session set NLS_DATE_FORMAT =' '; -- This change is only useful for the current session.

For example:

SQL> alter session set nls_date_format = 'yyyy-mm-dd'; the session has been changed. SQL> insert into abc (B) values ('2017-08-26 '); 1 row has been created.

--------------------------------------------------------------------------------
The built-in Oracle function SYSDATE can return the current date and time of the system, for example:
Select to_char (sysdate, 'dy DD-Mon-YYYY HH24: MI: ss') as "Current Time" from dual;
Two interesting things to note here:

You can use double quotes to make names case sensitive (by default, SQL is case insensitive), or to force spaces into names. oracle will treat everything inside the double quotes literally as a single name. in this example, if "Current Time" is not quoted, it wowould have been interpreted as two case insensitive names CURRENT and TIME, which wowould actually cause a syntax error. DUAL is built-in relation in Oracle which serves as a dummy relation to put in the FROM clause when nothing else is appropriate. for example, try "select 1 + 2 from dual ;". another name for the built-in function SYSDATE is CURRENT_DATE. be aware of these special names to avoid name conflicts.

--------------------------------------------------------------------------------
Operations on DATE You can compare DATE values using the standard comparison operators such as = ,! =,>, Etc.
You can subtract two DATE values, and the result is a FLOAT which is the number of days between the two DATE values. in general, the result may contain In a fraction because DATE also has a time component. for obvious reasons, adding, multiplying, and piding two DATE values are not allowed.

You can add and subtract constants to and from a DATE value, and these numbers will be interpreted as numbers of days. for example, SYSDATE + 1 will be tomorrow. you cannot multiply or pide DATE values.

With the help of TO_CHAR, string operations can be used on DATE values as well. For example, to_char ( , 'Dd-MON-YY ') like' % JUN % 'evaluates to true if Is in June.