Oracle - 如何将 VARCHAR 转换为 DATETIME 格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14951118/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle - How to Convert VARCHAR to DATETIME format
提问by Avadhani Y
I am using Oracle10gdatabase in which a table contains a Column with DateDataType. I am using the following query to get the record:
我正在使用Oracle10g数据库，其中一个表包含一个带有DateDataType的列。我正在使用以下查询来获取记录：
select to_char(START_TIME, 'YYMMDD HH24:MI:SS') from table;
So from above query, the result will be of type VARCHAR. I have tried to_Date()method but resulted in displaying only DATE. Can i convert VARCHARto DATETIMEformat? The result should be of type DATETIME. Please help me how to resolve this problem.
所以从上面的查询，结果将是类型VARCHAR。我尝试过to_Date()方法，但导致只显示DATE. 我可以转换VARCHAR到DATETIME格式？结果应该是类型DATETIME。请帮助我如何解决这个问题。
回答by DazzaL
an Oracle datecontains both date and time so you can consider it a datetime(there is no datatype of datetimein Oracle). how is DISPLAYS when you select it is entirely up to your client. the default display setting is controlled by the NLS_DATE_FORMATparameter. If you're just using the date in your pl/sql block then just assign it into a datedatatype and select into that variable without to_charand it will work just fine and contain whatever time component is present in your table.
Oracledate包含日期和时间，因此您可以将其视为 a datetime（datetimeOracle 中没有数据类型）。当您选择 DISPLAYS 时，它如何完全取决于您的客户。默认显示设置由NLS_DATE_FORMAT参数控制。如果你只是在你的 pl/sql 块中使用日期，那么只需将它分配到一个date数据类型中并选择该变量to_char，它就会正常工作并包含表中存在的任何时间组件。
to control the display, for example using nls_date_format:
控制显示，例如使用nls_date_format：
SQL> select a from datetest;
A
---------
19-FEB-13
SQL> alter session set nls_date_format='YYMMDD HH24:MI:SS';
Session altered.
SQL> select a from datetest;
A
---------------
130219 07:59:38
but again, this is only for display.
但同样，这仅用于显示。
回答by Grisha Weintraub
回答by Andrew Brennan
Yeah the Date datatype will meet your needs but you will have to jump through some hoops every time to get the exact time out of it. Definitely use the Timestamp datatype.
是的 Date 数据类型将满足您的需求，但您每次都必须跳过一些圈子才能从中获得确切的时间。绝对使用时间戳数据类型。
