Oracle modification Time: ORA-01830: Date format picture resolution to end before converting the entire input string

1. Cause of Error:
The date type cannot contain precision after seconds.
As Date: 2010-01-01 20:02:20.0

Workaround: Remove the precision after the date seconds, To_date (substr (invoice_date,1,10), ' Yyyy-mm-dd ')
If 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 ";".

3. Modify the database date format:
Alter session set nls_date_format= ' Yyyy-mm-dd hh24:mi:ss '

The scope of this command is to modify the current session, and this cannot modify the date format of the third-party tool, such as: PL/SQL developer needs to be Tools->preferences->user interface->date/ Time to modify the format, the date format of the third-party tools to set themselves inside the tool, 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 when the program is written, the same session is sure to produce the effect you want after the command is executed.

If you want to change the overall database-level default format to modify the parameter file, and restart the database, but this also can 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, third-party tools are converted But many times easy to be fooled by the tool, give the feeling is that the database is so stored, 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 requirements of the corresponding format to see the effect just.

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, the string and the following format to match; To_char is the corresponding format;

Oracle Date format Summary processing date Daquan in Oracle

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 digits 98
YYYY four digits 1998

The time range in the 24-hour format is: 0:00:00-23:59:59 ....
The time range in the 12-hour format is: 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

Showing the Hundred Twenty-two

3.
Ask what day it is
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
Setting the date language
ALTER SESSION SET nls_date_language= ' AMERICAN ';
You can do that.
To_date (' 2002-08-26 ', ' yyyy-mm-dd ', ' nls_date_language = American ')

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

5. The use of time 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 ')
It is not included in this range until after 12 o'clock noon on December 31 and before 12 o ' December 1.
So, when time needs to be accurate, I think to_char is still necessary.
7. Date format conflict issues
The input format depends on the type of Oracle character set you 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_language = American
or write in To_date.
Select To_char (to_date (' 2002-08-26 ', ' yyyy-mm-dd '), ' Day ', ' nls_date_language = American ') from dual;
Note that I'm just lifting the nls_date_language, and of course there's a lot of
To view
SELECT * FROM Nls_session_parameters
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 ')

Look for days between 2002-02-28 and 2002-02-01 except Monday and seven
Call Dbms_utility before and after each. Get_time, let's 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 set up 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 ') by 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 month and year day similar to this
13.
The treatment of the month and 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 deal with the indefinite number of months
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.

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

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

19.
5 Seconds an 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-bit seconds

20.
The day ordinal of a 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 the 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 following numbers are counted from Sunday onwards.
1 2 3 4 5 6 7
Day 123456

There are many functions for dates in Oracle

There are many functions about dates in Oracle, such as:
1, add_months () to increase or decrease some 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 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 pm-11:
00

Sql>

3. Current_timestamp () returns the current date in the current time zone with the timestamp with date 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 morning + 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 pm-11:
00

Sql>

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

DBTIME
------
-07:00

Sql>

5, extract () Find the field value of the date or interval 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 containing 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 () 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 that was indicated by the second argument for the first time out of the day (should return the name string for the corresponding day)

Date function with week

1. Query the first day of a week
Select Trunc (WW, decode, 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. Check the last day of a week
Select Trunc (WW, decode, 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 in-time operations

The forum often see there is a question of the Oracle time operation questions, there is time today, look at the previous brothers of the post, collated a bit, and made an example, I hope it will be helpful to everyone.
First of all, thank Ern, Eric.li and the moderator and enthusiastic brothers

The contents are as follows:
1, Oracle supports the operation of the date
2, the date is calculated in days as a unit
3, when it is necessary to calculate the value of smaller units, such as seconds, time-based conversion can be
4. Pay attention to parentheses when making time-conversion conversions (see red brackets in the example), or there will be a problem

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.

Oracle modification Time: ORA-01830: Date format picture resolution to end before converting the entire input string