如何在 PL/SQL 中将日期格式从 MM/DD/YYYY 更改为 YYYY-MM-DD？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7606745/
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
How to change the date format from MM/DD/YYYY to YYYY-MM-DD in PL/SQL?
提问by user972548
I have a date column in a table stored as MM/DD/YYYYformat. I have to select and store the same date in another table in YYYY-MM-DDformat i.e. XSD Date Format. But I am not able to do it. I am using this query:
我在存储为MM/DD/YYYY格式的表中有一个日期列。我必须在另一个表中选择并存储相同的日期YYYY-MM-DD格式，即 XSD 日期格式。但我做不到。我正在使用这个查询：
select to_date(date_column,'YYYY-MM-DD') from table;
But still I am not able to do it. Giving me error
但我仍然无法做到。给我错误
ORA-01843 : not a valid month
ORA-01843 : 不是有效月份
回答by Yahia
use
用
select to_char(date_column,'YYYY-MM-DD') from table;
回答by Jon Skeet
It sounds like you've got it the wrong way round. If your existingdata is in MM/DD/YYYY format, then you want:
这听起来像你弄错了方法。如果您现有的数据采用 MM/DD/YYYY 格式，那么您需要：
select to_date(date_column,'MM/DD/YYYY') from table;
to convert the existing data to DATE values. (I do wonder why they're not stored as dates, to be honest...)
将现有数据转换为 DATE 值。（老实说，我确实想知道为什么它们不存储为日期......）
If you want to perform the conversion in one step, you might want:
如果您想一步执行转换，您可能需要：
select to_char(to_date(date_column,'MM/DD/YYYY'), 'YYYY-MM-DD') from table;
In other words, for each row, parse it in MM/DD/YYYY format, then reformat it to YYYY-MM-DD format.
换句话说，对于每一行，将其解析为 MM/DD/YYYY 格式，然后将其重新格式化为 YYYY-MM-DD 格式。
(I'd still suggest trying to keep data in its "natural" type though, rather than storing it as text in the first place.)
（不过，我仍然建议尝试将数据保留为“自然”类型，而不是首先将其存储为文本。）
回答by Jorge E. Hernández
I assume that you can use the Oracle SQL Developer, which you can download from here.
我假设您可以使用 Oracle SQL Developer，您可以从这里下载。
You can define the date format which you want to work with:
您可以定义要使用的日期格式：
ALTER SESSION SET nls_date_format='yyyy-mm-dd';
With this, now you can perform a query like this:
有了这个，现在你可以执行这样的查询：
SELECT * FROM emp_company WHERE JDate = '2014-02-25'
If you want to be more specific you can define the date format like this:
如果你想更具体，你可以像这样定义日期格式：
ALTER SESSION SET nls_date_format='yyyy-mm-dd hh24:mi:ss';
回答by Phate01
To convert a DATEcolumn to another format, just use TO_CHAR()with the desired format, then convert it back to a DATEtype:
要将DATE列转换为另一种格式，只需使用TO_CHAR()所需的格式，然后将其转换回DATE类型：
SELECT TO_DATE(TO_CHAR(date_column, 'DD-MM-YYYY'), 'DD-MM-YYYY') from my_table
回答by Aravind Krishnakumar
This worked for me! You can convert to datatype you want be it a date or string
这对我有用！您可以转换为您想要的数据类型，无论是日期还是字符串
to_char(TO_DATE(TO_CHAR(end_date),'MM-DD-YYYY'),'YYYY-MM-DD') AS end_date
回答by user9338150
select to_date(to_char(ORDER_DATE,'YYYY/MM/DD'))
from ORDERS;
This might help but, at the end you will get a string not the date. Apparently, your format problem will get solved for sure .
这可能会有所帮助，但最后你会得到一个字符串而不是日期。显然，您的格式问题肯定会得到解决。
回答by Jenna Leaf
For military time formatting,
对于军事时间格式，
select TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mm:ss') from DUAL
--2018-07-10 15:07:15
If you want your date to round DOWN to Month, Day, Hour, Minute, you can try
如果您希望日期四舍五入为月、日、小时、分钟，您可以尝试
SELECT TO_CHAR( SYSDATE, 'yyyy-mm-dd hh24:mi:ss') "full-date" --2018-07-11 10:40:26
, TO_CHAR( TRUNC(SYSDATE, 'year'), 'yyyy-mm-dd hh24:mi:ss') "trunc-to-year"-- 2018-01-01 00:00:00
, TO_CHAR( TRUNC(SYSDATE, 'month'), 'yyyy-mm-dd hh24:mi:ss') "trunc-to-month" -- 2018-07-01 00:00:00
, TO_CHAR( TRUNC(SYSDATE, 'day'), 'yyyy-mm-dd hh24:mi:ss') "trunc-to-Sunday" -- 2018-07-08 00:00:00
, TO_CHAR( TRUNC(SYSDATE, 'dd'), 'yyyy-mm-dd hh24:mi:ss') "trunc-to-day" -- 2018-07-11 00:00:00
, TO_CHAR( TRUNC(SYSDATE, 'hh'), 'yyyy-mm-dd hh24:mi:ss') "trunc-to-hour" -- 2018-07-11 10:00:00
, TO_CHAR( TRUNC(SYSDATE, 'mi'), 'yyyy-mm-dd hh24:mi:ss') "trunc-to-minute" -- 2018-07-11 10:40:00
from DUAL
For formats literals, you can find help in https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions242.htm#SQLRF52037
对于格式文字，您可以在https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions242.htm#SQLRF52037 中找到帮助
回答by supernova
Basically , Data in a Date column in Oracle can be stored in any user defined format or kept as default. It all depends on NLS parameter.
基本上，Oracle 中日期列中的数据可以以任何用户定义的格式存储或保留为默认格式。这一切都取决于 NLS 参数。
Current format can be seen by : SELECT SYSDATE FROM DUAL;
当前格式可以通过以下方式查看：SELECT SYSDATE FROM DUAL;
If you try to insert a record and insert statement is NOT in THIS format then it will give : ORA-01843 : not a valid month error. So first change the database date format before insert statements ( I am assuming you have bulk load of insert statements) and then execute insert script.
如果您尝试插入记录并且插入语句不是这种格式，那么它会给出：ORA-01843：无效月份错误。因此，首先在插入语句之前更改数据库日期格式（我假设您有大量插入语句），然后执行插入脚本。
Format can be changed by : ALTER SESSION SET nls_date_format = 'mm/dd/yyyy hh24:mi:ss';
可以通过以下方式更改格式：ALTER SESSION SET nls_date_format = 'mm/dd/yyyy hh24:mi:ss';
Also You can Change NLS settings from SQL Developer GUI , (Tools > preference> database > NLS)
您也可以从 SQL Developer GUI 更改 NLS 设置，（工具 > 首选项 > 数据库 > NLS）
参考：http: //oracle.ittoolbox.com/groups/technical-functional/oracle-sql-l/how-to-view-current-date-format-1992815
回答by Johannes
According to the comments, the data-type in the datatable is DATE. So you should simply use: "select date_column from table;"
根据评论，数据表中的数据类型是DATE。所以你应该简单地使用：“select date_column from table;”
Now if you execute the select you will get back a date data-type, which should be what you need for the .xsd.
现在，如果您执行选择，您将返回一个日期数据类型，这应该是 .xsd 所需要的。
Culture-dependent formating of the date should be done in the GUI (most languages have convenient ways to do so), not in the select-statement.
日期的文化相关格式应该在 GUI 中完成（大多数语言都有这样做的方便方法），而不是在选择语句中。
回答by akanksha gupta
You can do this simply by :
您可以简单地通过以下方式做到这一点：
select to_char(to_date(date_column, 'MM/DD/YYYY'), 'YYYY-MM-DD') from table
