Java Save Yyyy-mm-dd HH:mm:ss date string to Oracle Date Type field [Original]__c++

+ "', '" + mytechnical + "', to_date (' +mybirthday+ ', ' yyyy-mm-dd '), '" + Myemail + "', '" + mytelephone

To_date (', ') does not need to be added to the SQL statement, but in order to get the variable it must be in the above format

First, use the Oracle function:

To_timestamp_tz (' 2009-3-9 17:51:23.23-05:00 ', ' yyyy-mm-d HH24:MI:SS. FF Tzh:tzm ')

To_date (yourdate, ' yyyy-mm-d HH24:MI:SS '); String Go Date

T0_char (yourdate, ' yyyy-mm-d HH24:MI:SS '); Date Day Spin string

eg

Update t_fl_flownote set Sendtime=to_timestamp_tz (' 2009-11-30 10:47:16 ', ' yyyy-mm-dd HH24:MI:SS ')

Second, transfer other people's diary

We all know that date and timestamp are representations of dates and times, except that the two types of precision are different, the former is accurate to the second, the latter is accurate to decimal seconds (fractional_seconds_precision), can be 0 to 9, and the default is 6.

However, the operation of the date type is simple, with many functions available for processing, while the difference between the two timestamp is visually displayed for how many days + hours + How many minutes + seconds + how many decimal seconds,

Sql> CREATE TABLE Test (T1 TIMESTAMP (6),
2 T2 TIMESTAMP (6));

Table has been created.

sql> INSERT into test values (
2 To_timestamp (' 2006-01-01 12:10:10.1 ', ' yyyy-mm-dd hh24:mi:ss.ff '),
3 To_timestamp (' 2006-01-01 12:20:10.2 ', ' yyyy-mm-dd hh24:mi:ss.ff '));

1 lines have been created.

Sql>
sql> INSERT into test values (
2 To_timestamp (' 2006-01-01 12:10:10.1 ', ' yyyy-mm-dd hh24:mi:ss.ff '),
3 To_timestamp (' 2006-01-02 12:20:10.2 ', ' yyyy-mm-dd hh24:mi:ss.ff '));

1 lines have been created.

Sql>
sql> INSERT into test values (
2 To_timestamp (' 2006-01-01 12:10:10.1 ', ' yyyy-mm-dd hh24:mi:ss.ff '),
3 To_timestamp (' 2006-01-02 13:40:20.2 ', ' yyyy-mm-dd hh24:mi:ss.ff '));

1 lines have been created.

Sql> commit;

Submit completed.

Sql>

The difference of two timestamp is very intuitive to show how many days + hours + How many minutes + how many seconds + how many decimal seconds:
Sql> select T2-t1 from test;
+000000000 00:10:00.100000
+000000001 00:10:00.100000
+000000001 01:30:10.100000

Sql>

But simply converting to a certain precision is cumbersome, and using a similar date-type approach is not going to work. If the conversion is divided into:
Sql> Select 1440* (t2-t1) from test;
+000000010 00:02:24.000000000
+000001450 00:02:24.000000000
+000001530 04:02:24.000000000

Sql>

found that the result is not originally wanted, but in the original "How many days + hours + How many minutes + how many seconds + how many decimal seconds" every item is multiplied by 1440 before processing.

The easiest thing to understand is to use the substr to divide the two timestamp's difference into the process:

Sql> SELECT substr (T2-T1), InStr ((T2-T1), "') +7,2 seconds,
2 substr ((T2-T1), InStr ((T2-T1), "+4,2") minutes,
3 substr ((T2-T1), InStr ((T2-T1), "+1,2") hours,
4 trunc (To_number (substr (T2-T1), 1,instr (T2-t1, ')))
5 trunc (To_number (substr (T2-T1), 1,instr (T2-t1, '))/7) weeks
6 from Test;

SECO Minu HOUR Days WEEKS
---- ---- ---- ---------- ----------
00 10 00 0 0
00 10 00 1 0
10 30 01 1 0

Or use a custom function to convert the number of days to the "Day and Minutes" format:

CREATE OR REPLACE FUNCTION f_days2str (p_days in number DEFAULT 0)
Return VARCHAR2 is
--ver:1.0
--created by XSB on 2005-05-26
--for: Convert days to minutes and seconds
Daysnumber: = NVL (p_days, 0);
Vdnumber; --Days
Vhnumber; --hour
Vmnumber; --Divided
Vsnumber; --seconds
Result VARCHAR2 (100); --Return value
BEGIN
VD: = TRUNC (days);
VH: = TRUNC ((DAYS-VD) * 24);
VM: = TRUNC ((days-vd-vh/24) * 24 *60);
VS: = TRUNC ((DAYS-VD-VH/24-VM/24/60) * 24 * 60 *60);
SELECT DECODE (VD, 0, ', VD | | ') Days ') | | DECODE (VH, 0, ", VH) | ' Hour ') | | DECODE (vm,0, ", VM | | ' min ') | | DECODE (vs,0, ", VS. | | ' seconds ') into the result from DUAL;
return (result);
End;

Sql>

If the accuracy of the final result is not high (in cent or above), you can convert the timestamp to date and then settle, which is much simpler:

Sql> Select (To_date to_char (T2, ' yyyy-mm-dd hh24:mi:ss '), ' Yyyy-mm-dd hh24:mi:ss '
)-to_date (To_char (T1, ' Yyyy-mm-dd hh24:mi:ss '), ' Yyyy-mm-dd hh24:mi:ss ') *24*60
2 from Test;

10

1450

1530.16667

Date converted to timestamp