ORA-01846: invalid Week in Japan

2010-05-28  来源:本站原创  分类:Database  人气:205 

SQL> select to_date ('Thu Dec 17 14:04:23 2009', 'DY MON DD HH24: MI: SS YYYY') from dual;

Error Line 1:
ORA-01846: invalid Week in Japan

Modification method: alter session set nls_date_language = 'american';

Reverse Change:

alter session set nls_date_language = 'SIMPLIFIED CHINESE

to_date () format reserved for backup:

TO_DATE format (with time :2007-11-02 13:45:25 example)

Year:
yy two digits show the value of two years: 07
yyy three digits show the value of three years: 007
yyyy four digits show the value of four years: 2007

Month:
mm number two on display value: 11
mon abbreviated character set that shows the value: 11 months, if the English, show nov
month spelled out the character set that shows the value: 11 months, if the English, show november

Day:
dd number the first few days show that the value of the month: 02
ddd number shows the value then the first few days: 02
dy abbreviated first days of the week abbreviated display value: Friday, if English, show fri
day spelled out the first days of the week to write the whole show value: Friday, if English, show friday
ddspth spelled out, ordinal twelfth

Hour:
hh two digits 12 Hours hex display value: 01
hh24 two digits 24 Hours hex display value: 13

Minute:
mi two digits 60 hexadecimal display value: 45

Second:
ss two digits 60 hexadecimal display value: 25

Other
Q digit quarter shows the value: 4
WW digit display value then the first couple of weeks: 44
W digit the first few weeks show that the value of the month: a

24-hour format time range: 0:00:00 - 23:59:59 ....
12-hour format time range: 1:00:00 - 12:59:59 ....

1. Date and character conversion function usage (to_date, to_char)

select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') as nowTime from dual; / / date is converted to string
select to_char (sysdate, 'yyyy') as nowYear from dual; / / get time in
select to_char (sysdate, 'mm') as nowMonth from dual; / / get time on
select to_char (sysdate, 'dd') as nowDay from dual; / / Get the daily time
select to_char (sysdate, 'hh24') as nowHour from dual; / / obtain the time when
select to_char (sysdate, 'mi') as nowMinute from dual; / / get the time points
select to_char (sysdate, 'ss') as nowSecond from dual; / / get the time seconds

select to_date ('2004-05-07 13:23:44 ',' yyyy-mm-dd hh24: mi: ss') from dual / /

2.
select to_char (to_date (222, 'J'), 'Jsp') from dual

Display Two Hundred Twenty-Two

3. Seeking one 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 the date language
ALTER SESSION SET NLS_DATE_LANGUAGE = 'AMERICAN';
It can also be
TO_DATE ('2002-08-26 ',' YYYY-mm-dd ',' NLS_DATE_LANGUAGE = American ')

4. The number of days between two dates
select floor (sysdate - to_date ('20020405 ',' yyyymmdd ')) from dual;

5. Time is the use of null
select id, active_date from table1
UNION
select 1, TO_DATE (null) from dual;

Note that to use TO_DATE (null)

6. Months difference
a_date between to_date ('20011201 ',' yyyymmdd ') and to_date ('20011231', 'yyyymmdd')
Then at 12 o'clock on the December 31 and December 1 after a 12-point before the scope is not included within this.
So when the time when the need for accurate, that is necessary to_char

7. Date format input format conflict depends on your installation type ORACLE character set, such as: US7ASCII, date format type 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 me to cite only NLS_DATE_LANGUAGE, of course, many
To see
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')

Find 2002-02-28 to 2002-02-01 Apart from the number of days on Monday and seven were in before and after the call DBMS_UTILITY.GET_TIME, so that after subtracting the results (get is 1 / 100 seconds, not milliseconds).

9. Find the month
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

10. Next_day usage
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 first record of the TIME and the last line is the same as a function can be established to deal with 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
extract () to find a date or interval value of the field values
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

13. Years on treatment
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)), 12) MONTHS,
newer_date,
older_date
from (
select hiredate older_date, add_months (hiredate, rownum) + rownum newer_date
from emp
)
)

14. Dealing with uncertain number of days in the month approaches
select to_char (add_months (last_day (sysdate) +1, -2), 'yyyymmdd'), last_day (sysdate) from dual

16. To find out the number of days this year
select add_months (trunc (sysdate, 'year'), 12) - trunc (sysdate, 'year') from dual

Handling of leap year
to_char (last_day (to_date ('02 '| |: year,' mmyyyy ')),' dd ')
If it is 28, is not a leap year

17.yyyy difference with rrrr
'YYYY99 TO_C
------- ----
yyyy 99 0099
rrrr 99 1999
yyyy 01 0001
rrrr 01 2001

18. Do not deal with time zones
select to_char (NEW_TIME (sysdate, 'GMT', 'EST'), 'dd / mm / yyyy hh: mi: ss'), sysdate
from dual;

An interval of 19.5 seconds
Select TO_DATE (FLOOR (TO_CHAR (sysdate, 'SSSSS') / 300) * 300, 'SSSSS'), TO_CHAR (sysdate, 'SSSSS')
from dual

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

20. The first few days a year
select TO_CHAR (SYSDATE, 'DDD'), sysdate from dual

3102002-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 in
floor ((date2-date1, 365) / 30) as the month
d (mod (date2-date1, 365), 30) as the date.

23.next_day function returns the next week the date, day for the 1-7 or Sunday - Saturday, Sunday, 1 means
next_day (sysdate, 6) is from the current starting next Friday. The figures behind the counting from Sunday onwards.
1234567
Day 123 456

-------------------------------------------------- -------------

select (sysdate-to_date ('2003-12-03 12:55:45 ',' yyyy-mm-dd hh24: mi: ss')) * 24 * 60 * 60 from ddual
Date back to the days and then converted to ss

24, round [round to the nearest date] (day: rounding to the nearest Sunday)
select sysdate S1,
round (sysdate) S2,
round (sysdate, 'year') YEAR,
round (sysdate, 'month') MONTH,
round (sysdate, 'day') DAY from dual

25, trunc [truncated to the nearest date, units of days], return to the date type
select sysdate S1,
trunc (sysdate) S2, / / returns the current date, not when the minutes and seconds
trunc (sysdate, 'year') YEAR, / / return the current year on January 1, minutes and seconds when no
trunc (sysdate, 'month') MONTH, / / return the current month 1, minutes and seconds when no
trunc (sysdate, 'day') DAY / / return the current week of Sunday, no time minutes and seconds
from dual

26, the latest date of return to date list
select greatest ('01-1 Month -04 ', '04-January -04', '10-February -04 ') from dual

27. Calculate the time difference Note: oracle time difference is the number of days as a unit, so conversion into years, Japan

select floor (to_number (sysdate-to_date ('2007-11-02 15:55:03 ',' yyyy-mm-dd hh24: mi: ss')) / 365) as spanYears from dual / / time difference - in
select ceil (moths_between (sysdate-to_date ('2007-11-02 15:55:03 ',' yyyy-mm-dd hh24: mi: ss'))) as spanMonths from dual / / time difference - Monthly
select floor (to_number (sysdate-to_date ('2007-11-02 15:55:03 ',' yyyy-mm-dd hh24: mi: ss'))) as spanDays from dual / / time difference - days
select floor (to_number (sysdate-to_date ('2007-11-02 15:55:03 ',' yyyy-mm-dd hh24: mi: ss')) * 24) as spanHours from dual / / time lag - the time
select floor (to_number (sysdate-to_date ('2007-11-02 15:55:03 ',' yyyy-mm-dd hh24: mi: ss')) * 24 * 60) as spanMinutes from dual / / time difference - minutes
select floor (to_number (sysdate-to_date ('2007-11-02 15:55:03 ',' yyyy-mm-dd hh24: mi: ss')) * 24 * 60 * 60) as spanSeconds from dual / / time difference - seconds

28. Update Note: oracle time to add and subtract is the number of days as a unit, set to change the amount of n, so converted into years, Japan
select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss'), to_char (sysdate + n * 365, 'yyyy-mm-dd hh24: mi: ss') as newTime from dual / / change the time - years
select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss'), add_months (sysdate, n) as newTime from dual / / change the time - months
select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss'), to_char (sysdate + n, 'yyyy-mm-dd hh24: mi: ss') as newTime from dual / / change time - day
select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss'), to_char (sysdate + n/24, 'yyyy-mm-dd hh24: mi: ss') as newTime from dual / / change the time - when
select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss'), to_char (sysdate + n/24/60, 'yyyy-mm-dd hh24: mi: ss') as newTime from dual / / change the time - minutes
select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss'), to_char (sysdate + n/24/60/60, 'yyyy-mm-dd hh24: mi: ss') as newTime from dual / / change time - seconds

29. Find the first day of the month, the last day
SELECT Trunc (Trunc (SYSDATE, 'MONTH') - 1, 'MONTH') First_Day_Last_Month,
Trunc (SYSDATE, 'MONTH') - 1 / 86400 Last_Day_Last_Month,
Trunc (SYSDATE, 'MONTH') First_Day_Cur_Month,
LAST_DAY (Trunc (SYSDATE, 'MONTH')) + 1 - 1 / 86400 Last_Day_Cur_Month
FROM dual;

相关文章
  • ORA-01846: invalid Week in Japan 2010-05-28

    SQL> select to_date ('Thu Dec 17 14:04:23 2009', 'DY MON DD HH24: MI: SS YYYY') from dual; Error Line 1: ORA-01846: invalid Week in Japan Modification method: alter session set nls_date_language = 'american'; Reverse Change: alter session set nls_dat

  • Data Dump Issue - ORA-02304 2010-12-13

    Trying to put a dump file with impdp from one schema into another schema (two in the same database schema, you need to use remap_schema parameters for the mapping schema conversion) encountered many similar to the following error .... (Cmd> impdp tes

  • (R) Oracle development topics of: computing time 2011-05-24

    1.Oracle date functions 2. Date Modified 3 months plus or minus 4 years plus or minus 5. Find the last day of each month 6 seek first day of each month 7. Seeking the next week Started: ① Oracle date and time in storage: oracle time format stored in

  • birt Cannot access NLS data files or invalid environment 2010-07-27

    ORA-12705: Cannot access NLS data files or invalid environment specified Articles Category: Database solutions are as follows: 1, First open the "Control Panel "-->" item "-->" Regional Regional and Language Options" ca

  • oracle function to licensing issues (00904 identifier is invalid) 2011-03-10

    If the function is created under a schema, for example, established under the A function function test (). And then to use the B. I run a GRANT EXECUTE ON "A". "Test" TO "B"; so user B after A.test () is, but using test () wi

  • ORA-12705: Cannot access NLS data files or invalid environment specified error 2011-03-11

    ORA-12705: Cannot access NLS data files or invalid environment specified error Free install the Oracle client to use PL / SQL, TOAD FOR oracle connection manager such as Oracle9i, 10g As we all know, Oralce client a few megabytes too big, there are m

  • ORA-28595: Extproc agent: Invalid DLL Path 2011-05-13

    ORACLE to store GT_GEOMETRY in a time of the ORA-28595: Extproc agent: Invalid DLL Path. Inquiries to the following solution (in the following steps to change the path accordingly): Oracle Net Services to use ST_Geometry SQL functions: ST_Geometry in

  • ORA-600错误[kkoipt:invalid join method] 2012-03-09

    ORA-600错误[kkoipt:invalid join method] 今天客户的11.2.0.2数据库碰到了ORA-600这个错误. 详细错误信息如下: Fri Sep 16 15:23:52 2011 Errors in file /u01/diag/rdbms/ora1/ora1/trace/ora1_ora_20382140.trc (incident=169704): ORA-00600: 内部错误代码, 参数: [kkoipt:invalid join method], [1],

  • Update rails when the error happened, Invalid Gem format 2009-03-15

    Today put the version of rails upgrade to 2.2.2 when the following errors happen. ERROR: Error installing rails: invalid gem format for c:/ruby/lib/ruby/gems/1.8/cache/rails-2.2.2.gem Put c: / ruby/lib/ruby/gems/1.8/cache/rails-2.2.2.gem delete this

  • Spring + Hibernate, CRD no problem, update invalid?? 2009-03-29

    Yes my spring2.5.6 + hibernte3.3, did not use spring and hibernateTemplate the HibernateDaoSupport, only its AnnotationSessionFactoryBean, Tim censored search do not have any problem, as long as one update on the invalid, the console is not error. I

  • Japan vs domestic project success factors of outsourcing projects large contrast 2009-08-08

    Last 3 years has been against the project on outsourcing, has recently made a national project, some feelings, some summary >>>>>>> Demand. Outsourcing Situation: on the demand side is given a clear and complete in writing of Success

  • java.sql.SQLException: ORA-00911: invalid character 2009-10-30

    Been the morning debugging .. java.sql.SQLException: ORA-00911: invalid characters in this error .. Repeatedly to check the database there is no problem debugging .. no way to come up with the ultimate trump card .. GOOGLE. Found http://blog.csdn.net

  • C3P0 Error: java.sql.SQLException: Connection is invalid 2010-03-29

    2010-02-20 11:01:16,093 WARN [com.mchange.v2.async.ThreadPoolAsynchronousRunner] -- com.mchange.v2.async.ThreadPoolAsynchronousRunner $ DeadlockDetector @ 16a3360 - APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks! 2010-02

  • FAQ: The binary you uploaded was invalid. The signature was invalid, or it was n 2010-03-29

    This is a major bug in Apple's iTunesConnect website. Occasionally, when you see this error message, it is actually the correct message. For most people, though, it's completely the wrong error message - and there is * no way * for you to find out wh

  • Bean property''is not writable or has an invalid setter method because 2010-03-29

    Bean property ' ' is not writable or has an invalid setter method. Does the parameter type of the setter match the return type of the getter? Implementation of the service because I did not inject dao Injection can be: private ShopRewardRecordDao sho

  • Analysis of the object name is invalid 2010-03-29

    To do press release today, the operation of test the following questions when com.microsoft.sqlserver.jdbc.SQLServerException: Object name is invalid That the real problem lies with the database Of a link statement public final static String URL = "j

  • Error javax.servlet.jsp.JspException: Invalid argument looking up property 2010-03-29

    Encountered an error org.apache.catalina.core.ApplicationDispatcher invoke Serious: Servlet.service () for servlet jsp threw exception javax.servlet.jsp.JspException: Invalid argument looking up property: "bean.property" of bean: "org.apach

  • createSQLQuery reported a "column name is invalid" error solution 2010-04-11

    Hql example, for the Select po.Id as (productOrder.intId) From o_product_order po, then the productOrder the mapping file can only have intId value of this attribute, if there is another attribute value, it will appear column name is invalid error. T

  • [YUI's AutoComplete] "forceSelection" option can prevent the user to enter an invalid value 2009-05-15

    Set properties myAutoComp.forceSelection = true; allows the user to not enter an invalid value - if you enter a non-candidate value, input box will be empty. This attribute can be used in conjunction with selectionEnforceEvent. A user enters an inval

  • With regard to the error occurred Invalid chunk ignored 2008-05-28

    Today, with the struts2 made a program, but the page when the report submitted by a warning: Parameters: Invalid chunk ignored Me anxious not, who told how before me, did not come into contact with it? There is no way, and then in online search, fina