в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T10:01:42Z

1 версия

Новая страница

==Combine to_char, add_months and to_date function==



<source lang="sql">

SQL>
SQL> SELECT TO_CHAR(ADD_MONTHS(TO_DATE("01-JAN-2005 19:15:26","DD-MON-YYYY HH24:
MI:SS"), 2), "DD-MON-YYYY HH24:MI:SS") FROM dual;
TO_CHAR(ADD_MONTHS(T
--------------------
01-MAR-2005 19:15:26
SQL>

</source>

==TO_CHAR(ADD_MON(TO_DATE("29-FEB-2000","DD-MON-YYYY"),1),"DD-MON-YYYY")==



<source lang="sql">

SQL>
SQL> SELECT TO_CHAR(ADD_MON(TO_DATE("29-FEB-2000","DD-MON-YYYY"),1),"DD-MON-YYYY")
2 FROM dual;
TO_CHAR(ADD
-----------
29-MAR-2000
1 row selected.
SQL>
SQL>
SQL> --

</source>

==to_date() and insert statement==



<source lang="sql">

SQL>
SQL> create sequence s_emp_id
2 start with 1000;
Sequence created.
SQL>
SQL>
SQL> create table gift(
2 gift_id integer primary key
3 ,emp_id integer
4 ,register_date date not null
5 ,total_price number(7,2)
6 ,deliver_date date
7 ,deliver_time varchar2(7)
8 ,payment varchar2(2)
9 ,emp_no number(3,0)
10 ,deliver_name varchar2(35)
11 ,message varchar2(100)
12 );
Table created.
SQL>
SQL> insert into gift(gift_id,emp_id,register_date,total_price,deliver_date,deliver_time,payment,emp_no,deliver_name,message)values
2 (1,1,"14-Feb-1999", 123.12, "14-Feb-1999", "12 noon", "CA",1, null, "Happy Birthday to you");
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time ,payment ,emp_no,deliver_name ,message)values
2 (2,1,"14-Feb-1999", 50.98, "14-feb-1999", "1 pm", "CA",7, "name1", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (3, 2,"14-Feb-1999", 35.99, "14-feb-1999", "1 pm", "VS",2, "Tom", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (4, 2,"14-Feb-1999", 19.95, "14-feb-1999", "5 pm", "CA",2, "Mary", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
2 (5, 6,"4-mar-1999", 10.95, "5-mar-1999", "4:30 pm", "VS", 2, "Jack", "Happy Birthday");
1 row created.
SQL>
SQL>
SQL> insert into gift (gift_id, emp_id, register_date, payment)
2 values (s_emp_id.nextval, s_emp_id.currval,
3 to_date("01/15/2000", "mm/dd/yyyy"), "VS");
1 row created.
SQL>
SQL>
SQL> drop table gift;
Table dropped.
SQL>
SQL> drop sequence s_emp_id;
Sequence dropped.
SQL>

</source>

==Use to_date in insert statement==



<source lang="sql">

SQL>
SQL>
SQL> create table author_log(
2 activity clob,
3 completed date
4 )
5 /
Table created.
SQL>
SQL>
SQL> insert into author_log values (
2 "Began SQL Functions appendix",
3 to_date( "04-NOV-2001 08:00", "DD-MON-YYYY HH24:MI" )
4 )
5 /
1 row created.
SQL>
SQL> select * from author_log;
ACTIVITY COMPLETED
-------------------------------------------------------------------------------- ---------
Began SQL Functions appendix 04-NOV-01
1 row selected.
SQL>
SQL> drop table author_log;
Table dropped.
SQL>
SQL> --

</source>

==Using TO_DATE Within PL/SQL==



<source lang="sql">

SQL>
SQL> DECLARE
2 v_Convert_Date DATE;
3 BEGIN
4 v_Convert_Date := TO_DATE("031092","MMDDYY");
5 DBMS_OUTPUT.PUT_LINE("The converted date is: " || v_Convert_Date);
6 END;
7 /
The converted date is: 10-MAR-2092 00:00:00
PL/SQL procedure successfully completed.
SQL>
SQL> --

</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/Date Timezone/TO DATE - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия