Processing of date fields in Oracle

(1) In the English version of Oracle, the default date format is 'dd-MON-YY ', for example, '01-JAN-98'

In the Chinese version of the Chinese version, the default ORACLE date format is 'day-month-Year', for example, '21-January-2003-'or '21-January-03'

(2) convert string to date
Use the ORACLE internal function to_date ()

The to_date () function parameter is the inverse of the to_char () function parameter.

To_date (string_value, date_format)

The variables are defined as follows:

String_value: Return Value of a string, a string column (a column of a table defined in the database), or a string internal function.

Date_format is a valid Oracle date format.

The following update statements comply with the ORACLE date format and are successfully updated:

Create table student (name varchar2 (10) not null primary key, enrolldate date not null); // create a 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', '21-January 1, August-2003 ');

 

Insert into xiongxiaomin. student values ('mark3', '21-January 1, August-03 ');

(3) convert the date to a string
Use the ORACLE internal function to_char ()
To_char (date_value, date_format)

The variables are defined as follows:

Date_value: The date type value returned by the date type direct value (date itself), the date type column value (a column of a table defined in the database), or an internal function.

Date_format is a valid Oracle date format.

 

Select the time part of the enrolldate field:

Select (to_char (enrolldate, 'hh24: MI: ss') enrolldate from student;

 
(4) Note: columns defined by the date data type in ORACLE include date and time.

1. The current system date is2003Year 8Month 21DayAnd then execute the following statements:

Insert into student values ('mark5', to_date ('09: 28: 03', 'hh: MI: ss ')); // note that this method inserts time into the date Field of a record. The default date of the system is the first day of the month of the current date.

 

Name enrolldate

 

Mark501-August-2003 09:28:03 AM

 

2. modify the current system date2003Year 7Month 21DayAnd then execute the following statements:

Insert into student values ('mark6', to_date ('09: 28: 03', 'hh: MI: ss '));

The resulting record is

Name enrolldate

 

Mark601-July-2003 09:28:03 AM

 

Insert into student values ('mark8', to_date ('20: 28: 03', 'hh24: MI: ss '));

 
3. Insert a date and time in a record at the same time

Insert into student values ('mark9', to_date ('21-08-2003

20:28:03 ', 'dd-MM-YYYY HH24: MI: ss'); // Method for inserting a date and time in a record at the same time

 

Insert into student values ('mark', to_date ('21-08-2003

09:52:03 ', 'dd-MM-YYYY HH: MI: ss'); // Method for inserting a date and time in a record at the same time

 
Note: you cannot use the following method to insert a date and a time at the same time.

Insert into student values ('mark2', '21-January 1, August-2003 '+ to_date ('

09:52:03 ', 'hh: MI: ss'); // The method for inserting invalid records

 
4. Run the following SQL statement in SQL * Plus Worksheet:

Select * from student;

The record result is as follows: note that only the date and time fields are displayed in the date field.

NAME ENROLLDATE

 

Mark 18-8-03

Mark1 21-8-03

Mark2 21-8-03

 

 

 

3 rows have been selected.

 
 
 
 

5. However, in the JSP program, select * from student order by enrolldate is also executed.

For the display result, see (date and time are displayed)

 

Display Results on the JSP page

6. Run the following SQL statement in SQL * Plus Worksheet:

Select name, to_char (enrolldate, 'dd-MM-YYYYHH: MI: SS') From student;

The record result is as follows: note that the date field shows the date and time.

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: 5 And 6 Time to second, JSP ". 0" is displayed on the page. "
7. Execute the following SQL statement in SQL * Plus Worksheet

Select name, to_char (enrolldate, 'dd-MM-YYYYHH24: MI: SS') From student;

The record result is as follows: note that the date type field shows the date and 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"HH: MI: SS"And"HH24: MI: SS"The difference between the two is that" HH "is an hour, the value range is 0-12;" HH24 "is a 24-hour system, the value range is 0-23.

See the following example:

① Insert into student values ('southari ', to_date ('08-19-2003
13:54:05 ', 'Mm-DD-YYYY HH24: MI: ss '));
The record result in ORACLE is:
NAME enrolldate
South tairi 19-August-2003 01:54:05
② Insert into student values ('southari 2', to_date ('08-19-2003
13:54:05 ', 'Mm-DD-YYYY HH: MI: ss'); // insert error
Error message:

Insert into student values ('southari 2', to_date ('08-19-2003 13:54:05 ', 'Mm-DD

*

ERROR is located in row 1st:

ORA-01849: The Hour value must be between 1 and 12

That is, when "HH" is applied, "13:54:05" cannot be applied. Instead, it should be changed to "01:54:05"

The correct statement is as follows:

Insert into student values ('southari 2', to_date ('08-19-2003

01:54:05 ', 'Mm-DD-YYYY HH: MI: ss '));

The record result in ORACLE is:
NAME enrolldate

South Pacific 2 19-March August-2003 01:54:05 AM
Note that one result is PM and the other is AM.

We recommend that you use the "HH24" Method to Improve the correctness"

8, but in the JSP program also execute select name, to_char (enrolldate, 'dd-MM-YYYY HH: MI: ss') from student

For the displayed results, see. (The result is equivalent to executing the following SQL statement in SQL * Plus Worksheet.

Select name, to_char (enrolldate, 'dd-MM-YYYY HH: MI: ss') from student; same)

9. Run the following SQL statement in SQL * Plus Worksheet:

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

The record result is as follows: note that the date type field shows the date and time

NAME TO_CHAR (ENROLLDATE, 'monthdd, YYYYHH24: MI: ss ')

 

Mark August 18 17:40:20

Mark1 August 21 00:00:00

Mark2 August 21 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 is different

'Dd-MM-YYYY 'format: 21-08-2003

'Month dd, yyyy' format: August 21

 

11. Note: the format of month and second (MM and MI) is very confusing in the date and time formats)

Run the following statement in SQLPlus * Worksheet. No error is reported.

Insert into student values ('wang jia', to_date ('08:05', 'Hh24:MM'); // 08 is considered as the hour, and 05 is considered as the month.

Commit;

In the table Editor, check that the actual inserted value is

NAME ENROLLDATE

Wang Jia 01-May-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 jia01-05-2003 08:00:00

Select 1 line.

Insert into student values ('wangjia 2', to_date ('08-19-2003

13:54:05 ', 'Mm-DD-YYYY HH24: MI: ss '));

 
 

12. Another advantage of Oracle's date data type is that it supports date calculation. You can

 

Date plus or minus several days, for example:

Insert into student values ('xiong ', sysdate + 7 );