How to insert a date and time into the Date field in an Oracle table ____oracle

To use Oracle 9i in your work, you often insert the date and time that the event occurred to one of the tables. Write a note specifically on Oracle's date and time display and how it is inserted.

Like the built-in data types such as NUMBER,VARCHAR2, Oracle uses date, the built-in data type, to store dates and times. As with MS SQL Server, dates and times are stored in a data type, and there is no single time or date data type that stores only time or dates. The date data type stores the date and time of the month.

When displaying date-type data, Oracle first converts the stored value from an internally saved format to an output string. Typically, this transformation is done by to_char this function. If the To_char function is not used to specify a specific date-time format, the Oracle default display format is "Dd-mon-yy". For example, we created a table x:

CREATE table x (a int, b date);

Then we execute the following query statement:

We will see the following result (set head on):

b
------------ 
01-apr-08 
(language is English) 

or 

b
------------- 
01-4 months -08 
(language is Chinese)

That is, each time a numeric value of date type is displayed, Oracle automatically calls the To_char function and displays the format as a parameter in the default date. We can explicitly call the To_char function and specify the format that we want. For example:

SELECT To_char (b, ' Yyyy/mm/dd ') as b from X;

The result returned is:

B
------------ 
2008/04/01

To_char is a powerful function that converts the Date,mlslabel,number type of data into the VARCHAR2 type of data in the specified format, where we only care about the conversion of the date time. Grammar:

To_char (d [, fmt [, ' Nlsparams ']])

D is a date-type variable, and FMT is the datetime format we specify, if you do not explicitly specify the default value for Oracle. The usual date-time placeholders in FMT are as follows: MM the month (for example, a) MON abbreviated month name (for example, June) MONTH full month name (for example, July) DD date (for example,) DY day (for example, FRI) YYYY Year in 4-bit notation (for example, 2008) YY is a 2-bit year, and the two-bit (for example,) RR of the year is similar to YY, but the two-digit year is approximated to the year in the range 1950 to 2049, for example 06 is considered 2006 rather than 1906 AM (or PM indicator HH 12 in time (1-12) HH24 24 (0-23) MI minutes (0-59) SS seconds (0-59)

The above is about using the To_char function to display date-type data, and here's how to insert a date-type value into the table. At this point we are going to use the To_date function to convert the date of the string representation to the type of date.

The To_char function is implicitly called when Oracle displays DateTime, and when Oracle expects a value of type date, it implicitly invokes the To_date function to convert the input string, depending on the default format "Dd-mon-yy".

Or take our X-tables for example, we can directly enter:

INSERT into x values ("31-may-08");

However, regardless of the format used in the display will not affect the data we actually store, and when inserting, simply using Oracle's default format is not so consistent with the job needs, we still need to explicitly call the To_date function, for example:

INSERT into x values (to_date (' 2008/05/31:12:00:00am ', ' Yyyy/mm/dd:hh:mi:ssam '));

Syntax for to_date functions:

To_date (char [, FMT [, ' Nlsparams ']])

Char is a string that represents a date and time. The FMT representation method is the same as the To_char function.

We've mentioned earlier that Oracle's default date-time format is "Dd-mon-yy", in fact, we can modify this default format and change it to the format we need. Enter the following command in the Sql*plus:

Alter session set nls_date_format= ' <my_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 changed.
sql> INSERT INTO X (b) VALUES (' 2004-08-26 ');
1 lines have been created.

The Oracle built in 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:

can use double quotes to make names case sensitive (by default, SQL are case insensitive), or to force spaces into name S. Oracle would treat everything inside the double quotes literally as a single name. In this example, if ' current time ' is ' not quoted ', it would have been interpreted as two case insensitive Time, which would 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 approp Riate. For example, try "select 1+2 from Dual;". Another name for the built-in function sysdate is current_date. Be aware of this special names to avoid name conflicts. Operations on date 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 # of days between the two date values. In general, the result could contain a fraction because DATE also has a time component. For obvious reasons, adding, multiplying, and dividing two the DATE values are not allowed.

You can add and subtract constants to and from a DATE value, and these numbers'll be interpreted as numbers of the days. For example, sysdate+1 'll be tomorrow. You cannot multiply or divide DATE values.

With the help of To_char, string operations can is used on DATE values as. For example, To_char (<date>, ' dd-mon-yy ') like '%jun% ' evaluates to True if <date> are in June.