| How to convert to the new format [message #10907] |
Mon, 23 February 2004 19:55  |
Steven
Messages: 13
Registered: May 1999
|
Junior Member |
|
|
Dear Sir
We have a field
eg: Field1: varchar2(20)
the data input into the field like that dd/mm/yyyy.
Could we convert this fields to the new format like yyyy-mm-dd
using to_date or others.
Thanks in advance
Regards
Steven
|
|
|
|
| Re: How to convert to the new format [message #10910 is a reply to message #10907] |
Tue, 24 February 2004 02:11  |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You can convert the format using either to_date and to_char or using substr. However, it would be better if your field1 were of date datatype instead of varchar2; Then you could use to_char or nls_date_format to display it however you want and any date comparisons can be more easily and accurately done. Please see the examples below.
scott@ORA92> DESC your_table
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
FIELD1 VARCHAR2(20)
scott@ORA92> SELECT * FROM your_table
2 /
FIELD1
--------------------
24/02/2004
scott@ORA92> UPDATE your_table
2 SET field1 = TO_CHAR (TO_DATE (field1, 'dd/mm/yyyy'), 'yyyy-mm-dd')
3 /
1 row updated.
scott@ORA92> SELECT * FROM your_table
2 /
FIELD1
--------------------
2004-02-24
scott@ORA92> ROLLBACK
2 /
Rollback complete.
scott@ORA92> SELECT * FROM your_table
2 /
FIELD1
--------------------
24/02/2004
scott@ORA92> UPDATE your_table
2 SET field1 = SUBSTR (field1, 7, 4)
3 || '-' || SUBSTR (field1, 4, 2)
4 || '-' || SUBSTR (field1, 1, 2)
5 /
1 row updated.
scott@ORA92> SELECT * FROM your_table
2 /
FIELD1
--------------------
2004-02-24
scott@ORA92> ROLLBACK
2 /
Rollback complete.
scott@ORA92> ALTER TABLE your_table ADD temp_col DATE
2 /
Table altered.
scott@ORA92> UPDATE your_table
2 SET temp_col = TO_DATE (field1, 'dd/mm/yyyy'),
3 field1 = NULL
4 /
1 row updated.
scott@ORA92> ALTER TABLE your_table MODIFY field1 DATE
2 /
Table altered.
scott@ORA92> UPDATE your_table
2 SET field1 = temp_col
3 /
1 row updated.
scott@ORA92> ALTER TABLE your_table DROP COLUMN temp_col
2 /
Table altered.
scott@ORA92> DESC your_table
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
FIELD1 DATE
scott@ORA92> SELECT * FROM your_table
2 /
FIELD1
-----------
24-FEB-2004
scott@ORA92> SELECT TO_CHAR (field1, 'yyyy-mm-dd') FROM your_table
2 /
TO_CHAR(FI
----------
2004-02-24
scott@ORA92> ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd'
2 /
Session altered.
scott@ORA92> SELECT field1 FROM your_table
2 /
FIELD1
----------
2004-02-24
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
