Processing methods for Oracle Date Type fields _oracle

(1) in the English version of Oracle, the default date format is ' Dd-mon-yy ', such as ' 01-jan-98 '

The Oracle default date format in Chinese version is ' Day-month-year ', for example ' 2 January-August-2003 ' or ' 2 January-August-03 '

(2) Convert string to date

Using Oracle Internal Functions to_date ()

The parameter of the To_date () function is the inversion of the To_char () function argument.

To_date (string_value, Date_format)

The variables are defined as follows:

String_value: The return value of a string's direct value (the string itself), a string column (a column of a table defined in the database), or a string intrinsic function.

Date_format is a valid Oracle date format.

The following update statements are consistent with the date format of Oracle and are updated successfully:

Copy Code code as follows:

CREATE table Student (name VARCHAR2 () NOT NULL primary key, Enrolldate date not NULL);//Creating Student Table
　
INSERT into student values (' Mark ', To_date (' 08-21-2003 ', ' mm-dd-yyyy '));
　
INSERT into student values (' Mark1 ', to_date (' 21-08-2003 ', ' dd-mm-yyyy '));
　
INSERT into student values (' Mark2 ', ' 2 January-August-2003 ');

INSERT into xiongxiaomin.student values (' Mark3 ', ' 2 January-August-03 ');

(3) Convert date to String

Using Oracle Internal functions To_char ()
To_char (Date_value,date_format)

The variables are defined as follows:

Date_value: A date-type value for the date-type direct value (the date itself), the date-type column value (a column of a table defined in the database), or the return of an intrinsic function.

Date_format is a valid Oracle date format.

Select the time portion of the Enrolldate field:

Select (To_char (enrolldate, ' HH24:MI:SS ')) enrolldate from student;

(4) Note: The columns defined by the date data type in Oracle contain the date and time.

1. The current date of the system is August 21, 2003, after which the following statements are executed

INSERT into student values (' Mark5 ', to_date (' 09:28:03 ', ' HH:MI:SS '))//Note the method of inserting time in the Date Type field of a record, The default date for the system is the first day of the month of the system's current date, so the record is

Name enrolldate

MARK5 01-8 months -2003 09:28:03 AM

2, modify the system current date is July 21, 2003, then execute the following statement

INSERT into student values (' Mark6 ', to_date (' 09:28:03 ', ' HH:MI:SS '));

The record obtained is

Name enrolldate

Mark6 01-7 months -2003 09:28:03 AM

INSERT into student values (' Mark8 ', to_date (' 20:28:03 ', ' HH24:MI:SS '));

3. Method of inserting date and time in one record at the same time

INSERT into student values (' Mark9 ', to_date (' 21-08-2003

20:28:03 ', ' dd-mm-yyyy HH24:MI:SS ')//the method of inserting dates and times in a record

INSERT into student values (' Mark ', To_date (' 21-08-2003

09:52:03 ', ' dd-mm-yyyy HH:MI:SS ')//the method of inserting dates and times in a record

Note You cannot insert dates and times at the same time using the following methods

INSERT into student values (' Mark2 ', ' 2 January-August -2003 ' +to_date (')

09:52:03 ', ' HH:MI:SS '))/Invalid Insert record method

4. Execute the following SQL statements in Sql*plus Worksheet

SELECT * from student;

The resulting record results are as follows: Note that only dates are displayed in the Date Type field and the time is not displayed

NAME enrolldate

Mark 1 August-August-03

Mark1 2 January-August-03

MARK2 2 January-August-03

3 rows have been selected.

5, but in the JSP program also executes the SELECT * FROM Student ORDER by enrolldate

The results are shown in the following figure (showing both the date and the time)

Display results in a JSP page

6. Execute the following SQL statements in Sql*plus Worksheet

Select Name,to_char (enrolldate, ' dd-mm-yyyy HH:MI:SS ') from student;

The resulting record results are as follows: note the date and time are displayed in the Day Type field

NAME to_char (enrolldate, ' Dd-mm-yyyyhh:mi:ss

Mark 21-08-2003 12:00:00

Mark1 21-08-2003 12:00:00

2 rows selected
Note: The difference between 5 and 6, time to seconds, the JSP page appears when more than ". 0"

7. Execute the following SQL statements in Sql*plus Worksheet

Select Name,to_char (enrolldate, ' dd-mm-yyyy HH24:MI:SS ') from student;

The resulting record results are as follows: note the Date Type field shows the dates, time

NAME to_char (Enrolldate, ' Dd-mm-yyyyhh24:mi:

Mark 18-08-2003 17:40:20

Mark1 21-08-2003 00:00:00

MARK2 21-08-2003 00:00:00

3 rows have been selected.

Note: The difference between "HH:MI:SS" and "HH24:MI:SS" in the Select in 6 and 7 is that "HH" is an hourly system, the range of values is 0-12, the "HH24" is 24-hour, and the range of values is 0-23.

Look at the following examples:
①insert into student values (' South Taihang ', To_date (' 08-19-2003
13:54:05 ', ' mm-dd-yyyy HH24:MI:SS '));
The results of records in Oracle are:
NAME enrolldate
Nancy 19-8 months -2003 01:54:05 PM
②insert into student values (' Nancy 2 ', To_date (' 08-19-2003
13:54:05 ', ' mm-dd-yyyy HH:MI:SS '));/Insert Error
The error message is:

INSERT into student values (' Nancy 2 ', To_date (' 08-19-2003 13:54:05 ', ' mm-dd

*

ERROR is on line 1th:

ORA-01849: Small values must be between 1 and 12

That is, "HH" can not be applied "13:54:05", but should be changed to "01:54:05"

The correct statement is as follows:

INSERT into student values (' Nancy 2 ', To_date (' 08-19-2003

01:54:05 ', ' mm-dd-yyyy HH:MI:SS '));
The results of records in Oracle are:
NAME enrolldate

Nancy 2 19-8 months -2003 01:54:05 AM
Note that the results of either of these methods are PM and the other is am.

In order to improve the correctness, the suggestion is to use the ground one way "HH24"

8, but in the JSP program also executes select Name,to_char (enrolldate, ' dd-mm-yyyy HH:MI:SS ') from student

The display results are shown in the following figure. (The result is the following SQL statement executed in sql*plus worksheet

Select Name,to_char (enrolldate, ' dd-mm-yyyy HH:MI:SS ') from student;

9. Execute the following SQL statements in Sql*plus Worksheet

Select Name,to_char (enrolldate, ' month dd,yyyy HH24:MI:SS ') from student;

The resulting record results are as follows: note the Date Type field shows the dates, time

NAME to_char (enrolldate, ' monthdd,yyyyhh24:mi:ss ')

Mark August 18,2003 17:40:20.

Mark1 August 21,2003 00:00:00

MARK2 August 21,2003 00:00:00

3 rows have been selected.
10, To_char () function, ' dd-mm-yyyy ' format and ' mm-dd-yyyy ' format and ' month DD,YYYY ' format are different

' dd-mm-yyyy ' format: 21-08-2003

' Month dd,yyyy ' format: August 21,2003

11, note: In the date and time format is very easy to confuse the month and the second format (MM and MI)

Execute the following statement in Sqlplus*worksheet without an error.

INSERT into student values (' Wang Jia ', to_date (' 08:05 ', ' hh24:mm '));//08 is considered an hour, and 05 is considered the month.

Commit

Viewing in Table Editor: The actual insertion value is

NAME enrolldate

Wang Jia 01-5 months -2003 08:00:00 AM

Select Name,to_char (enrolldate, ' dd-mm-yyyy HH24:MI:SS ') Enrolldate from

Student where name= ' Wang Jia ';

NAME enrolldate

Wang Jia 01-05-2003 08:00:00

1 rows have been selected.

INSERT into student values (' Wang Jia 2 ', to_date (' 08-19-2003

13:54:05 ', ' mm-dd-yyyy HH24:MI:SS '));

12. Another advantage of using Oracle's date data type is that it supports the operation of dates. Can be on the existing

Date plus or minus several days, for example:

INSERT into student values (' Bear ', sysdate+7);

The above is the entire content of this article, I hope to give you a reference, but also hope that we support the cloud habitat community.