SQL 如何在 Oracle 中为日期添加前导零？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16585608/
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 can I add leading zeros to dates in Oracle?
提问by Jivan
I need to add leading zeros to a number if it is less than two digits and combine two such numbers into single one without space between them.
如果一个数字少于两位数，我需要在数字上添加前导零，并将两个这样的数字组合成一个，它们之间没有空格。
My Attempt:
我的尝试：
select ( extract (year from t.Dt)
|| to_char(extract (month from t.Dt),'09')
|| to_char(extract (day from t.Dt),'09') ) as dayid
from ATM_FACTS t;
Result:
结果：
So, my problem is how can I remove the space in between month-year and month-day. I used
所以，我的问题是如何删除月-年和月-日之间的空间。我用了
select ( extract (year from t.Dt)
|| to_number(to_char(extract (month from t.Dt),'09'))
|| to_number(to_char(extract (day from t.Dt),'09')) ) as dayid
from ATM_FACTS t;
but the leading zeros disappear.
但前导零消失了。
回答by Ben
It doesn't look like you want to add leading zero's, it looks like you're not converting your date to a character in exactly the way you want. The datetime format modelof TO_CHAR() is extremely powerful, make full use of it.
看起来您不想添加前导零，看起来您没有完全按照您想要的方式将日期转换为字符。TO_CHAR()的日期时间格式模型非常强大，充分利用它。
select to_char(dt, 'yyyymmdd') as dayid
from atm_facts
To actually answer your question you can use a number format modelwith TO_CHAR() to pad with leading 's.
要实际回答您的问题，您可以使用带有 TO_CHAR()的数字格式模型来填充前导。
For instance, the following returns 006
例如，以下返回 006
select to_char(6, 'fm009') from dual;
You can use the format model modifier fm, mentioned in the docs above, to remove leading spaces if necessary.
fm如有必要，您可以使用上面文档中提到的格式模型修饰符来删除前导空格。
回答by nvoigt
Is t.Dt a DATE? You can format them in one single to_charstatement:
t.Dt 是日期吗？您可以在一个to_char语句中格式化它们：
to_char(t.Dt, 'YYYYMMDD')
