如何在Oracle中为日期添加前导零? [英] How can I add leading zeros to dates in Oracle?

问题描述

如果数字小于两位数，我需要在数字前添加零，并将两个这样的数字组合成一个数字，并且它们之间没有空格.

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.

我的尝试:

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;

结果:

所以，我的问题是如何在月年和月日之间删除空间.我用过

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;

但前导零消失了.

推荐答案

您似乎不希望添加前导零，您似乎并没有按照想要的方式将日期转换为字符. TO_CHAR()的 datetime格式模型极其强大，可以完全使用它.

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 model of TO_CHAR() is extremely powerful, make full use of it.

select to_char(dt, 'yyyymmdd') as dayid
  from atm_facts

要真正回答您的问题，您可以使用数字格式模型用TO_CHAR()来填充前导'.

To actually answer your question you can use a number format model with TO_CHAR() to pad with leading 's.

例如，以下返回006

select to_char(6, 'fm009') from dual;

如有必要，您可以使用上面的文档中提到的格式模型修饰符fm来删除前导空格.

You can use the format model modifier fm, mentioned in the docs above, to remove leading spaces if necessary.

这篇关于如何在Oracle中为日期添加前导零?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！