Oracle modification Time: ORA-01830: Date format Picture The solution that ends before converting the entire input string __oracle

1. Error Reason:
The date type cannot contain the precision after the second.
As Date: 2010-01-01 20:02:20.0

Workaround: Remove the precision after the date seconds
As a date: 2010-01-01 20:02:20

2, INSERT into TEST2 (C1, C2,C3) VALUES (${v1},${v2},to_date (${v3}, ' Yyyy-mm-dd '));
Oracle does not need to end with ";" inside.

3, modify the date format of the database:
Alter session set nls_date_format= ' Yyyy-mm-dd hh24:mi:ss '

The scope of this command is to modify the current session, in addition, this can not modify the date format of Third-party tools, such as: Pl/sql developer need in Tools->preferences->user interface->date/ Time to modify the format, the Third-party tool date format within the tool to set itself, I think you should also be under the Third-party tools to see this effect, generally by default, Oracle default date format is not the case.

In the default Sqlplus or SQLPLUSW or program writing process, after the execution of the command, if the same session is sure to produce the effect you want.

If you want to change the overall database-level default format to modify the parameter file, and restart the database, but this will not change your Third-party tool display format, Third-party tools can only be changed within the tool, it can only change the default date format, the Third-party tools are converted , but many times it's easy to be fooled by tools, the feeling is that the database is stored in this way, in fact, only the date format stored, no yyyymm these things, these things are calculated by the algorithm results, and then according to the format required for the corresponding format to see the effect of this.

4, INSERT into TEST2 (C1, C2,C3) VALUES (' 1 ', 1,to_date (' 2010-12-27 00:00:00 ', ' yyyy-mm-dd ')) can not
INSERT into TEST2 (C1, C2,C3) VALUES (1,1,to_date (' 2010-12-27 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')) can
To_date is a type conversion, not a format conversion, a string and a subsequent format to match; To_char is the corresponding format;

Oracle Date format Summary processing dates in Oracle Encyclopedia

To_date format
Day:
DD Number 12
DY abbreviated Fri
Day spelled out Friday
Ddspth spelled out, ordinal twelfth
Month:
MM Number 03
Mon abbreviated Mar
Month spelled out March
Year:
YY two digits 98
YYYY four digits 1998

24-hour format of the time range: 0:00:00-23:59:59 ....
12-hour format of the time range: 1:00:00-12:59:59 ....
1.
Date and character conversion function usage (TO_DATE,TO_CHAR)

2.
Select To_char (to_date (222, ' J '), ' JSP ') from dual

Show two hundred twenty-two

3.
How many days is the day of the week
Select To_char (to_date (' 2002-08-26 ', ' yyyy-mm-dd '), ' Day ') from dual;
Monday
Select To_char (to_date (' 2002-08-26 ', ' yyyy-mm-dd '), ' Day ', ' nls_date_language = American ') from dual;
Monday
Set Date language
ALTER session SET nls_date_language= ' American ';
I can do that.
To_date (' 2002-08-26 ', ' yyyy-mm-dd ', ' nls_date_language = American ')

4.
Days of two days
Select Floor (sysdate-to_date (' 20020405 ', ' YYYYMMDD ')) from dual;

5. The use of time as null
Select ID, active_date from table1
UNION
Select 1, to_date (null) from dual;

Note to use to_date (NULL)

6. 
A_date between to_date (' 20011201 ', ' YYYYMMDD ') and to_date (' 20011231 ', ' YYYYMMDD ')  
The December 31 is not included in this range until after 12 o'clock noon and December 1 of 12 points. &NBSP
So, when time needs to be accurate, feel to_char is still necessary  
7. Date format conflict  
Enter the format of the Oracle character set you have installed, such as: Us7ascii, The type of DATE format is: ' 01-jan-01 '  
alter system set nls_date_language = AMERICAN 
Alter session set Nls_date_lan guage = american 
or write   in to_date
Select To_char (to_date (' 2002-08-26 ', ' yyyy-mm-dd '), ' Day ', ' nls_date _language = American ') from dual; 
Notice I'm just lifting the nls_date_language, of course there are many,  
to view the  
Select * from Nls_ SESSION_PARAMETERS&NBSP
Select * from V$nls_parameters

8.
Select COUNT (*)
From (select Rownum-1 rnum
From All_objects
where RowNum <= to_date (' 2002-02-28 ', ' yyyy-mm-dd ')-To_date (' 2002-
02-01 ', ' yyyy-mm-dd ') +1
)
where To_char (to_date (' 2002-02-01 ', ' yyyy-mm-dd ') +rnum-1, ' D ')
Not
In (' 1 ', ' 7 ')

Find days between 2002-02-28 and 2002-02-01 with the exception of Monday and seven
The dbms_utility are called before and after each other. Get_time to subtract the result (get 1/100 seconds instead of milliseconds).

9.
Select Months_between (to_date (' 01-31-1999 ', ' mm-dd-yyyy '),
To_date (' 12-31-1998 ', ' mm-dd-yyyy ')) "MONTHS" from DUAL;
1

Select Months_between (to_date (' 02-01-1999 ', ' mm-dd-yyyy '),
To_date (' 12-31-1998 ', ' mm-dd-yyyy ')) "MONTHS" from DUAL;

1.03225806451613
Usage of Next_day
Next_day (date, day)

Monday-sunday, for Format code day
Mon-sun, for format code DY
1-7, for format code D

11
Select To_char (sysdate, ' hh:mi:ss ') time from all_objects
Note: The time of the first record is the same as the last line
You can create a function to handle this problem
Create or Replace function sys_date return date is
Begin
return sysdate;
End

Select To_char (sys_date, ' Hh:mi:ss ') from all_objects;
12.
Get the number of hours

SELECT EXTRACT (HOUR from TIMESTAMP ' 2001-02-16 2:38:40 ') from offer
Sql> Select Sysdate, To_char (sysdate, ' hh ') from dual;

Sysdate to_char (sysdate, ' HH ')
-------------------- ---------------------
2003-10-13 19:35:21 07

Sql> Select Sysdate, To_char (sysdate, ' hh24 ') from dual;

Sysdate to_char (sysdate, ' HH24 ')
-------------------- -----------------------
2003-10-13 19:35:21 19

Get the date of the year and similar
13.
The processing of the month and the day
Select Older_date,
Newer_date,
Years
Months
Abs
Trunc
newer_date-
Add_months (older_date,years*12+months)
)
) days
From (select
Trunc (Months_between (newer_date, older_date)/12) YEARS,
MoD (trunc (Months_between (Newer_date, older_date)),
) MONTHS,
Newer_date,
Older_date
From (select HireDate older_date,
Add_months (hiredate,rownum) +rownum newer_date
From EMP)
)

14.
Ways to handle days of the month
Select To_char (Add_months (Last_day (sysdate) +1,-2), ' YYYYMMDD '), Last_day (sysdate) from dual

16.
Find out the number of days this year
Select Add_months (trunc (Sysdate, ' year ')-Trunc (Sysdate, "year") from dual

How to deal with leap years
To_char (Last_day (to_date ' | |: Year, ' mmyyyy '), ' DD ')
If it's 28, it's not a leap year.

17.
The difference between YYYY and RRRR
' YYYY99 To_c
------- ----
YYYY 99 0099
RRRR 99 1999
yyyy 01 0001
Rrrr 01 2001

18. Processing of different time zones
Select To_char (New_time (sysdate, ' GMT ', ' EST '), ' dd/mm/yyyy hh:mi:ss '), sysdate
from dual;

19.
5 seconds, one interval.
Select to_date (FLOOR (To_char (sysdate, ' sssss ')/300) *, ' sssss '), To_char (sysdate, ' sssss ')
From dual

2002-11-1 9:55:00 35786
SSSSS represents 5-digit seconds

20.
The first day of the year
Select To_char (sysdate, ' DDD '), sysdate from dual
310 2002-11-6 10:03:51

21. Calculate hours, minutes, seconds, milliseconds
Select
Days,
A
TRUNC (a*24) Hours,
TRUNC (A*24*60-60*trunc (a*24)) Minutes,
TRUNC (A*24*60*60-60*trunc (a*24*60)) Seconds,
TRUNC (A*24*60*60*100-100*trunc (a*24*60*60)) mseconds
From
(
Select
Trunc (sysdate) days,
Sysdate-trunc (sysdate) A
From dual
)

SELECT * FROM TabName
Order by decode (mode, ' FIFO ', 1,-1) *to_char (RQ, ' Yyyymmddhh24miss ');

//
Floor ((date2-date1)/365) as the year
Floor ((date2-date1, 365)/30) as Month
MoD (mod (date2-date1, 365), 30) as day.
23.next_day function
Next_day (sysdate,6) is from the current beginning of the next Friday. The figures for the following are from Sunday onwards.
1 2 3 4 5 6 7
Day 123456

There are many functions about dates in Oracle

There are many functions about dates in Oracle, such as:
1, Add_months () is used to increase or decrease the number of months from a date value
Date_value:=add_months (date_value,number_of_months)
Cases:
Sql> Select Add_months (sysdate,12) "Next year" from dual;

Next year
----------
1 March-November-04

Sql> Select Add_months (sysdate,112) "Last year" from dual;

Last year
----------
1 March-March-13

Sql>

2. Current_date () returns the current date that will be placed in the time zone
Date_value:=current_date
sql> column Sessiontimezone for A15
Sql> select Sessiontimezone,current_date from dual;

Sessiontimezone Current_da
--------------- ----------
+08:00 1 March-November-03

Sql> alter session set Time_zone= ' -11:00 '
2/

The session has changed.

Sql> select Sessiontimezone,current_timestamp from dual;

Sessiontimezone Current_timestamp
--------------- ------------------------------------
-11:00 December-November-03 04.59.13.668000 Afternoon-11:
00

Sql>

3, Current_timestamp () returns the current date in the time zone by timestamp with the zone data type
Timestamp_with_time_zone_value:=current_timestamp ([timestamp_precision])
sql> column Sessiontimezone for A15
sql> Column Current_timestamp format A36
Sql> select Sessiontimezone,current_timestamp from dual;

Sessiontimezone Current_timestamp
--------------- ------------------------------------
+08:00 1 March-November-03 11.56.28.160000 a.m. + 08:
00

Sql> alter session set Time_zone= ' -11:00 '
2/

The session has changed.

Sql> select Sessiontimezone,current_timestamp from dual;

Sessiontimezone Current_timestamp
--------------- ------------------------------------
-11:00 December-November-03 04.58.00.243000 Afternoon-11:
00

Sql>

4, Dbtimezone () return time zone
Varchar_value:=dbtimezone
Sql> select Dbtimezone from dual;

DBTIME
------
-07:00

Sql>

5, extract () to find the date or interval value of the field value
Date_value:=extract (Date_field from [Datetime_value|interval_value])
Sql> Select Extract (month from sysdate) ' this month ' from dual;

This Month
----------
11

Sql> Select Extract (Year from add_months (sysdate,36)) "3 Years out" from dual;

3 Years out
-----------
2006

Sql>

6, Last_day () returns the date of the last day of the month that contains the date parameter
Date_value:=last_day (Date_value)
Sql> Select Last_day (Date ' 2000-02-01 ') "Leap Yr?" from dual;

Leap Yr?
----------
2 September-February-00

Sql> Select Last_day (sysdate) "Last day of this month" from dual;

Last Day O
----------
30月-November-03

Sql>

7, Localtimestamp () returns the date and time in the session
Timestamp_value:=localtimestamp
sql> Column Localtimestamp Format A28
Sql> select Localtimestamp from dual;

Localtimestamp
----------------------------
1 March-November-03 12.09.15.433000
Afternoon

Sql> select Localtimestamp,current_timestamp from dual;

Localtimestamp Current_timestamp
---------------------------- ------------------------------------
1 March-November-03 12.09.31.006000 1 March-November-03 12.09.31.006000 pm + 08:
Afternoon 00

Sql> alter session set time_zone= ' -11:00 ';

The session has changed.

Sql> Select Localtimestamp,to_char (sysdate, ' dd-mm-yyyy HH:MI:SS AM ') "Sysdate" from dual;

Localtimestamp sysdate
---------------------------- ------------------------
December-November-03 05.11.31.259000 13-11-2003 12:11:31 pm
Afternoon

Sql>

8, Months_between () to determine the number of months between two dates
Number_value:=months_between (Date_value,date_value)
Sql> Select Months_between (sysdate,date ' 1971-05-18 ') from dual;

Months_between (sysdate,date ' 1971-05-18 ')
----------------------------------------
389.855143

Sql> Select Months_between (sysdate,date ' 2001-01-01 ') from dual;

Months_between (sysdate,date ' 2001-01-01 ')
----------------------------------------
34.4035409

Sql>

9, Next_day () given a date value, returns the date value indicated by the second argument for the first time out now (the name string of the corresponding day should be returned)

With the week-related date functions

1. Query the first day of the week
Select Trunc (Decode (WW, to_date) (yy | | ' 3112 ', ' yyyyddmm '), To_date (yy | | '-' || To_char (WW * 7), ' yyyy-ddd '), ' d ') last_day
From (select substr (' 2004-32 ', 1, 4) yy, To_number (substr (' 2004-32 ', 6)) WW
From dual)

Select Trunc (to_date substr (' 2003-01 ', 1,5) | | To_char (To_number (substr (' 2003-01 ', 6)) *7), ' yyyy-ddd '), ' d ')-6 first_day from dual

Select min (v_date) from
(Select (To_date (' 200201 ', ' yyyymm ') + rownum) v_date
From All_tables
where RowNum < 370)
where To_char (v_date, ' yyyy-iw ') = ' 2002-49 '

2. Query the last day of the week
Select Trunc (Decode (WW, to_date) (yy | | ' 3112 ', ' yyyyddmm '), To_date (yy | | '-' || To_char (WW * 7), ' yyyy-ddd '), ' d ')-6 first_day
From (select substr (' 2004-33 ', 1, 4) yy, To_number (substr (' 2004-33 ', 6)) WW
From dual)

Select Trunc (to_date substr (' 2003-01 ', 1,5) | | To_char (To_number (substr (' 2003-01 ', 6)) *7), ' yyyy-ddd '), ' d ') Last_day from dual

Select Max (v_date) from
(Select (To_date (' 200408 ', ' yyyymm ') + rownum) v_date
From All_tables
where RowNum < 370)
where To_char (v_date, ' yyyy-iw ') = ' 2004-33 '

3. Query the date of a week
Select Min_date, To_char (min_date, ' Day ')
(Select To_date (substr (' 2004-33 ', 1,4) | | 001 ' +rownum-1, ' yyyyddd ') min_date
From All_tables
where rownum <= decode (mod (To_number (substr (' 2004-33 ', 1,4)), 4), 0,366,365)
Union

Select To_date (substr (' 2004-33 ', 1,4) -1| |
Decode (mod (To_number (substr (' 2004-33 ', 1,4)) -1,4, 0,359,358) +rownum, ' yyyyddd ') min_date
From All_tables
where RowNum <= 7
Union

Select To_date (substr (' 2004-33 ', 1,4) +1| | ' 001 ' +rownum-1, ' yyyyddd ') min_date
From All_tables
where RowNum <= 7
)
where To_char (min_date, ' yyyy-iw ') = ' 2004-33 '

Oracle-Time Operations

Forum often see there is a question of Oracle time calculation questions, today there is time to look at the previous brothers of the post, sorted out, and made an example, I hope it will help.
First of all, thanks to Ern, Eric.li and moderators and enthusiastic brothers.

The contents are as follows:
1. Oracle supports the operation of dates
2, date operation is in the days of the unit
3, when you need to be a minute or so smaller units to calculate the value, according to the time to convert the system can be
4. Pay attention to parentheses when making a time conversion (see examples of red parentheses), otherwise there will be problems

Sql> alter session set nls_date_format= ' Yyyy-mm-dd hh:mi:ss ';

The session has changed.

Sql> set Serverout on
Sql> Declare
2 DateValue date;
3 begin
4 Select Sysdate into DateValue from dual;
5 Dbms_output.put_line (' Source time: ' | | To_char (DateValue));
6 Dbms_output.put_line (' Source time minus 1 days: ' | | To_char (DateValue-1));
7 Dbms_output.put_line (' Source time minus 1 days 1 hours: ' | | To_char (DATEVALUE-1-1/24));
8 Dbms_output.put_line (' Source time minus 1 days 1 hours 1 minutes: ' | | To_char (datevalue-1-1/24-1/(24*60)));
9 Dbms_output.put_line (' Source time minus 1 days 1 hours 1 minutes 1 seconds: ' | | To_char (datevalue-1-1/24-1/(24*60) -1/(24*60*6
0));
Ten end;
11/
SOURCE Time: 2003-12-29 11:53:41
Source time minus 1 days: 2003-12-28 11:53:41
Source time minus 1 days 1 hours: 2003-12-28 10:53:41
Source time minus 1 days 1 hours 1 minutes: 2003-12-28 10:52:41
Source time minus 1 days 1 hours 1 minutes 1 seconds: 2003-12-28 10:52:40

The PL/SQL process has completed successfully.