oracle 将日期转换为另一种格式的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6211596/
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
SQL query to convert Date to another format
提问by Rohit
Thanks for your help. I am not able to make out the type/format of the "Value" in a Date column.I guess its in Julian Date format.
谢谢你的帮助。我无法确定日期列中“值”的类型/格式。我猜它是儒略日期格式。
The Column is paid_monthand the values are below.
列是 paid_month，值如下。
200901
200902
So,please help in writing SQL query to convert the above values(Mostly in Julian Format) in the Date Column to normal date (MM/DD/YYYY) .
因此，请帮助编写 SQL 查询以将日期列中的上述值（主要是儒略格式）转换为正常日期 (MM/DD/YYYY)。
Thanks Rohit
谢谢罗希特
Hi,
你好，
I am sorry for missing in giving the whole information.
我很抱歉没有提供全部信息。
1)Its a Oracle Database. 2)The column given is Paid_Month with values 200901,200902
1) 它是一个 Oracle 数据库。2) 给定的列是 Paid_Month，值为 200901,200902
3)I am also confused that the above value gives month & year.Day isnt given if my guess is right.
3）我也很困惑，如果我的猜测是对的，上面的值给出了月和年。没有给出日。
4)If its not in Julian format ,then also please help me the SQL to get at least mm/yyyy
4）如果它不是 Julian 格式，那么也请帮我 SQL 获得至少 mm/yyyy
I am using a Oracle DB and running the query THANKS i GOT THE ANSWER.
我正在使用 Oracle DB 并运行查询 谢谢我得到了答案。
**Now,i have to do the reverse meaning converting a date 01/09/2010 to a String which has 6 digits.Pls help with syntax- select to_char(01/01/2010,**
* *现在，我必须做相反的事情，将日期 01/09/2010 转换为具有 6 位数字的字符串。请帮助语法 - select to_char(01/01/2010,**
回答by Bohemian
It looks like YYYYMM - depending on your database variant, try STR_TO_DATE(paid_month, 'YYYYMM'), then format that.
它看起来像 YYYYMM - 根据您的数据库变体， try STR_TO_DATE(paid_month, 'YYYYMM')，然后格式化。
Note: MM/DD/YYYY is not "normal" format - only Americans use it. The rest of the world uses DD/MM/YYYY
注意：MM/DD/YYYY 不是“正常”格式——只有美国人使用它。世界其他地区使用 DD/MM/YYYY
回答by tofutim
For MySQL, you would use the STR_TO_DATE function, see http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_str-to-date
对于 MySQL，您将使用 STR_TO_DATE 函数，请参阅http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_str-to-date
SELECT STR_TO_DATE(paid_month,'%Y%m');
Sounds like the column contains some normal dates and some YYYYMM dates. If the goal is to update the entire column, you can attempt to isolate the YYYYMM dates and update only those. Something like:
听起来该列包含一些正常日期和一些 YYYYMM 日期。如果目标是更新整个列，您可以尝试隔离 YYYYMM 日期并仅更新这些日期。就像是：
UPDATE YourTable
SET paid_month = DATE_FORMAT(STR_TO_DATE(paid_month, '%Y%m'), '%m/%d/%Y')
WHERE LENGTH(paid_month) = 6
回答by user unknown
SELECT (paid_month % 100) + "/01/" + (paid_month/100) AS paid_day
FROM tbl;
I'm not sure about how oracle concatenates strings. Often, you see || in SQL:
我不确定 oracle 如何连接字符串。通常，您会看到 || 在 SQL 中：
SELECT foo || bar FROM ...
or functions:
或功能：
SELECT cat (foo, bar) FROM ...
回答by Carlos Gant
For MySQL check http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format
对于 MySQL 检查 http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format
Example:
例子：
SELECT DATE_FORMAT(NOW(), '%d/%m/%Y')
