使用 to_char 函数解析为字符串时，如何从 Oracle 中的日和月值中删除前导零？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22552498/
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 remove leading zeroes from day and month values in Oracle, when parsing to string using to_char function?
提问by Yulian
I want to retrieve a date without the leading zeroes in front of the day and month values in a select statement. If I execute the following query
我想在 select 语句中检索日期和月份值前没有前导零的日期。如果我执行以下查询
select to_char(sysdate, 'dd.mm.yyyy') from dual;
I will get 21.03.2014 as a result. Moreover, if today was, for example, 7th of March, 2014, I would get 07.03.2014. How can I get rid of these leading zeroes?
结果我会得到 21.03.2014。此外，如果今天是，例如，2014 年 3 月 7 日，我会得到 07.03.2014。我怎样才能摆脱这些前导零？
回答by Rob van Laarhoven
select to_char(sysdate,'DD.MM.YY') -- Without Fill Mode
, to_char(sysdate-20,'fmDD.fmMM.YY') -- With Fill Mode, 20 days ago
from dual;
Returns
退货
21.03.14 | 1.3.14
FM Fill mode.
FM 填充模式。
In a datetime format element of a TO_CHAR function, this modifier suppresses blanks in subsequent character elements (such as MONTH) and suppresses leading zeroes for subsequent number elements (such as MI) in a date format model. Without FM, the result of a character element is always right padded with blanks to a fixed length, and leading zeroes are always returned for a number element. With FM, which suppresses blank padding, the length of the return value may vary.
在 TO_CHAR 函数的日期时间格式元素中，此修饰符抑制后续字符元素（如 MONTH）中的空白，并抑制日期格式模型中后续数字元素（如 MI）的前导零。如果没有 FM，字符元素的结果总是用空格右填充到固定长度，并且总是为数字元素返回前导零。使用抑制空白填充的 FM，返回值的长度可能会有所不同。
回答by sen
Try this:
尝试这个：
select to_char(to_date('20-oct-2000'),'fmmm/dd/fmrr') from dual
select to_char(to_date('20-oct-2000'),'fmmm/dd/fmrr') from dual
The above query will remove the leading zeroes from day and month values in Oracle.
上述查询将从 Oracle 中的日和月值中删除前导零。
回答by pablomatico
This is quite far from being elegant but it works:
这远非优雅，但它有效：
select to_number(to_char(sysdate,'dd')) || '.' || to_number(to_char(sysdate,'mm')) || '.' || to_number(to_char(sysdate,'yyyy')) from dual
Basically, you would convert to number each part of the date
基本上，您将转换为日期的每个部分
