oracle to_date () use the details

2010-05-27  来源:本站原创  分类:Database  人气:211 

to_date () and 24-hour notation and mm-minute show:
First, the use of Oracle's to_date function to do date conversion, many Java programmers may be directly used "yyyy-MM-dd HH: mm: ss" format as the format conversion, but will cause errors in Oracle: "ORA 01810 format code appears twice."
Such as: select to_date ('2005-01-01 13:14:20 ',' yyyy-MM-dd HH24: mm: ss') from dual;
Reason is that SQL is not case sensitive, MM and mm are considered in the same format code, so Oracle's SQL mi instead of minutes used.
select to_date ('2005-01-01 13:14:20 ',' yyyy-MM-dd HH24: mi: ss') from dual;

Second, the other should be shown the form of 24-hour use HH24
select to_char (sysdate, 'yyyy-MM-dd HH24: mi: ss') from dual; / / mi is the minute
select to_char (sysdate, 'yyyy-MM-dd HH24: mm: ss') from dual; / / mm will show the month
the meaning of to_date oracle parameters
1. Explain the meaning of the date format parameter
D the week of the week
DAY-day name, padded with spaces to 9 characters
The first few days in mid-DD
In the first few days in the DDD
DY-day short name
IW ISO standard in the first few weeks
IYYY ISO standard four-year
Four Year YYYY
YYY, YY, Y the last three years, two, one
HH hours, based on a 12-hour
HH24-hour meter by 24-hour
MI points
SS seconds
MM month
Mon shorthand for the month
Month full name of the month
W the first few weeks of the month
WW in the first few weeks 1. The date of operation of the current time interval time of seven minutes minus select sysdate, sysdate - interval '7 'MINUTE from dual
The current time minus 7 hours time select sysdate - interval '7 'hour from dual
The current time minus 7 days select sysdate - interval '7 'day from dual
The current time minus the time in July select sysdate, sysdate - interval '7 'month from dual
The current time minus 7 years select sysdate, sysdate - interval '7 'year from dual
Interval multiplied by a number of select sysdate, sysdate - 8 * interval '2 'hour from dual
2. The date of the characters operating select sysdate, to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') from dual
select sysdate, to_char (sysdate, 'yyyy-mm-dd hh: mi: ss') from dual
select sysdate, to_char (sysdate, 'yyyy-ddd hh: mi: ss') from dual
select sysdate, to_char (sysdate, 'yyyy-mm iw-d hh: mi: ss') from dual
Reference to the relevant customs documentation oracle (ORACLE901DOC/SERVER.901/A90125/SQL_ELEMENTS4.HTM # 48515)
3. Characters to the date of operation select to_date ('2003-10-17 21:15:37 ',' yyyy-mm-dd hh24: mi: ss') from dual
Specific use and above to_char almost.
4. Trunk / ROUND function using select trunc (sysdate, 'YEAR') from dual
select trunc (sysdate) from dual
select to_char (trunc (sysdate, 'YYYY'), 'YYYY') from dual
5.oracle a millisecond-level data type - returns the current time of day hour minute and second years of milliseconds select to_char (current_timestamp (5), 'DD-MON-YYYY HH24: MI: SSxFF') from dual;
- Returns the current time, seconds, milliseconds, seconds later you can specify the precision (maximum = 9)
select to_char (current_timestamp (9), 'MI: SSxFF') from dual;
6. Computer program running time (ms)
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name% type;
l_start number default dbms_utility.get_time;
begin
for I in 1 .. 1000
loop
open l_rc for
'Select object_name from all_objects' | |
'Where object_id =' | | i;
fetch l_rc into l_dummy;
close l_rc;
end loop;
dbms_output.put_line
(Round ((dbms_utility.get_time-l_start) / 100, 2) | |
'Seconds ...');
end;

相关文章
  • oracle to_date () use the details 2010-05-27

    to_date () and 24-hour notation and mm-minute show: First, the use of Oracle's to_date function to do date conversion, many Java programmers may be directly used "yyyy-MM-dd HH: mm: ss" format as the format conversion, but will cause errors in O

  • Oracle TO_DATE date format Daquan 2010-12-10

    Transfer from http://apps.hi.baidu.com/share/detail/10070907 Oracle TO_DATE date format Daquan Oracle in the format 2009-04-14 10:53 TO_DATE TO_DATE format (with time :2007 -11-02 13:45:25 example) Year: yy two digits show the value of two years: 07

  • [Oracle] to_date () and to_char () Date and string conversion 2011-01-13

    [Oracle] to_date () and to_char () Date and string conversion to_date ("The string to convert," "convert format"), two parameters must match the format, otherwise it will error. The second parameter is the format in accordance with the

  • Oracle TO_DATE date format 2011-03-15

    The Oracle TO_DATE format 2009-04-14 10:53 TO_DATE format (:2007 -11-02 13:45:25 for 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

  • Oracle to_date()函数的用法介绍 2014-02-11

    to_date()是Oracle数据库函数的代表函数之一,下文对Oracle to_date()函数的几种用法作了详细的介绍说明,需要的朋友可以参考下 在Oracle数据库中,Oracle to_date()函数是我们经常使用的函数,下面就为您详细介绍Oracle to_date()函数的用法,希望可以对您有所启迪. to_date()与24小时制表示法及mm分钟的显示: 一.在使用Oracle的to_date函数来做日期转换时,很多Java程序员也许会直接的采用"yyyy-MM-dd HH:m

  • Oracle to_date format in 2010-04-07

    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:

  • oracle to_date example 2010-05-27

    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:

  • oracle to_date format in 3 2010-11-19

    7. Date format conflict Enter the format depends on your character set installed on the type of ORACLE, such as: US7ASCII, date format, type is: '01-Jan-01 ' alter system set NLS_DATE_LANGUAGE = American alter session set NLS_DATE_LANGUAGE = American

  • oracle to_date function is used in the Notes 2010-12-04

    First, in the use of Oracle's to_date function to do date conversion, many Java programmers may, like me, intuitive use of "yyyy-MM-dd HH: mm: ss" format as the format conversion, but in the Oracle will cause an error: "ORA 01810 format cod

  • Function usage in Oracle To_Date 2011-01-05

    to_date ('2003 / 07/09 ',' yyyy / mm / dd ') would return a date value of July 9, 2003. to_date ('070903 ',' MMDDYY ') would return a date value of July 9, 2003. to_date ('20020315 ',' yyyymmdd ') would return a date value of Mar 15, 2002. See `http:

  • oracle to_date () use 2011-03-20

    1, select to_date ('2011-01-01 00:00:00 ',' yyyy-mm-dd hh24: mi: ss') from dual; to_date (string, [format, [nlsparams]]) function char or varchar2 type value into a Date value. format, nlsparams is optional; format specifies the format to be converte

  • The date format: oracle to_date conversion in the implementation of measures to sqlserver 2011-05-18

    oracle in a convenient way to convert date format, as follows: to_date ('17-12-1980 ',' dd-mm-yyyy ') The same function in sqlserver in the realization of the following: cast ('1980-12-17 'as datetime)

  • oracle to_date format, date format and other methods 2011-07-09

    TO_DATE format (:2007 -11-02 13:45:25 for 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: 11 mon abb

  • Oracle TO_DATE and TO_CHAR to use the example of 2011-09-27

    TO_DATE format (time :2007 -11-02 13:45:25 for 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: 11 mo

  • Oracle to_date arguments detailed in the 2010-11-15

    D of the week day of the week DAY-day name, padded with spaces to 9 characters The first few days in mid-DD In the first days of DDD DY-day short name IW ISO standard in the first few weeks of IYYY ISO standard four-year Four Year YYYY YYY, YY, Y the

  • oracle to_date format in 2 2010-11-19

    13. Years at 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 (

  • oracle to_date function of time 2011-04-17

    To do fuzzy queries, we often use the time function: select * from student where birth> to_date ('2011-3-4 ',' yyyy-mm-dd hh24: mi: ss') / / based on the previous day, month to determine select * from student where birth> to_date ('2011-4-15 22:19:4

  • Oracle to_date date on the comparison interval 2010-05-13

    where a.id = b.godowinid and to_char (a.audittime, 'yyyy-mm-dd')> = istartdate and to_char (a.audittime, 'yyyy-mm-dd') <= ienddate Over time for comparison, if istartdate = '2010-05-13 ', ienddate = '2010-05-13' In fact, if the above time frame does

  • Oracle in the TO_DATE format 2010-03-09

    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:

  • Analyzing the details of Oracle JDBC driver 2010-09-09

    Oracle database to connect all the details of the oracle JDBC driver. These details include: 1, Import JDBC packages 2, registered oracle JDBC drivers 3, open the database connection 4, the implementation of SQL DML statements in the database table a