为什么日期列上的 RPAD() 仅返回日期部分?
我的表中有一个数据类型为 DATE
的列。当我通过查询获取列时(我使用 SYSDATE 作为示例,但行为是相同的),我得到了日期/时间 - 这是我所理解的。
SELECT SYSDATE
FROM DUAL
SYSDATE
--------------------
21-Feb-11 12:24:39 PM
现在,使用 rpad()
仅返回日期部分
SELECT SYSDATE, RPAD(SYSDATE, '9')
FROM DUAL
SYSDATE | RPAD(SYSDATE, '9')
----------------------|-------------------
21-Feb-11 12:27:14 PM | 21-FEB-11
Oracle 文档 指出:
RPAD 返回 expr1,用 expr2 右填充到长度 n 个字符,根据需要复制多次。如果 expr1 长于 n,则此函数返回 expr1 适合 n 的部分。
现在 sysdate 返回字符 > 9,那么为什么 rpad(16)
不返回日期和时间呢?
SELECT SYSDATE, RPAD(SYSDATE, '16')
FROM DUAL
SYSDATE | RPAD(SYSDATE, '16')
----------------------|-------------------
21-Feb-11 12:27:14 PM | 21-FEB-11
I have a column in a table with a data type as DATE
. When I fetch the column via a query (I've used SYSDATE
as an example, but the behavior is the same), I get the date/time - which I understand.
SELECT SYSDATE
FROM DUAL
SYSDATE
--------------------
21-Feb-11 12:24:39 PM
Now, using rpad()
returns only the date part
SELECT SYSDATE, RPAD(SYSDATE, '9')
FROM DUAL
SYSDATE | RPAD(SYSDATE, '9')
----------------------|-------------------
21-Feb-11 12:27:14 PM | 21-FEB-11
Oracle documentation states:
RPAD returns expr1, right-padded to length n characters with expr2, replicated as many times as necessary. If expr1 is longer than n, then this function returns the portion of expr1 that fits in n.
Now sysdate
returns characters > 9, so why doesn't, say rpad(16)
return the date and the time ?
SELECT SYSDATE, RPAD(SYSDATE, '16')
FROM DUAL
SYSDATE | RPAD(SYSDATE, '16')
----------------------|-------------------
21-Feb-11 12:27:14 PM | 21-FEB-11
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
RPAD 是一个字符串函数,因此当您将其应用于 DATE 值时,Oracle 首先必须将日期隐式转换为字符串,它使用会话的默认格式掩码(通常不包括时间部分)来执行此操作。试试这个:
话虽如此,当您选择 SYSDATE 时,您就会得到时间。如果我尝试复制您的案例,我会看到以下内容:
即几乎您希望看到的内容。这让我想知道:您如何设置格式以便 SELECT SYSDATE FROM DUAL 显示时间?
RPAD is a string function, so when you apply it to a DATE value Oracle first has to implicitly convert the date to a string, which it does using the session's default format mask, which usually does not include the time component. Try this instead:
Having said that, you are getting the time when you just select SYSDATE. If I try to replicate your case I see this:
i.e. pretty much what you were hoping to see. Which makes me wonder: how are you setting the format so that SELECT SYSDATE FROM DUAL shows the time?