使用 To_Date 函数未转换为预期格式

发布于 2025-01-17 01:13:54 字数 628 浏览 0 评论 0原文

我正在尝试使用 TO_DATE 将 char （从 substr 操作输出）转换为 dateTime 格式，格式如下 - hh24:mi:ss。

substr 的输出看起来不错，但一旦我通过 TO_DATE 函数运行它，它就会将此列的每一行转换为 01-MAR-22。

为了演示我有以下内容：-

SUS_TIME2 显示我从 SUBSTR 返回的内容，此时看起来很好。 SUS_TIME3 然后显示通过 TO_DATE 函数运行后我得到的结果，它将其转换为 01-MAR-22

,SUBSTR((s.resolve_date-suspend_date),-16,9) sus_time2

,TO_DATE(SUBSTR((s.resolve_date-suspend_date),-16,9), 'hh24:mi:ss') sus_time3

任何人都可以看到这里发生了什么请？谢谢

原文

I'm trying to convert a char (output from a substr operation) into a dateTime format using TO_DATE with the following format - hh24:mi:ss.

The output of the substr looks fine but as soon as I run it through the TO_DATE function it converts every row for this column into 01-MAR-22.

To demonstrate I have the following:-

SUS_TIME2 shows what I get back from the SUBSTR and it looks fine at this point. SUS_TIME3 then shows what I get back after running it through the TO_DATE function, this where it converts it to 01-MAR-22

,SUBSTR((s.resolve_date-suspend_date),-16,9) sus_time2

,TO_DATE(SUBSTR((s.resolve_date-suspend_date),-16,9), 'hh24:mi:ss') sus_time3

Can anyone see what's going on here please? Thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

坏尐絯 2025-01-24 01:13:54

日期以内部表示形式存储，没有任何固有格式。您所看到的是您的客户端如何选择使用会话的 NLS_DATE_FORMAT 设置将实际日期值格式化为字符串以进行显示（并非总是如此，但这是 SQL Developer，所以它在这里。）

当您调用 to_date() 仅时间部分，它会将日期部分默认为当前月份的第一天，这就是您看到 3 月 1 日的原因。这有点埋在文档中：

如果指定不带时间部分的日期值，则默认时间为午夜。如果您指定不带日期的日期值，则默认日期是当月的第一天。

但它在该日期正确设置了时间，您可以通过显式地将日期转换回字符串来查看：

to_char(<your date>, 'YYYY-MM-DD HH24:MI:SS')

或通过更改会话：

alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'

db>>fiddle

但是除了临时查询之外，您不应该依赖 NLS 设置进行任何操作；运行您的代码的其他人可能有不同的设置。并且只有在必须以特定格式显示值时才转换回字符串 - 存储它并将其作为本机日期传递。

Oracle 没有仅时间数据类型，因此如果您确实只关心时间部分，那么您可以使用日期（默认为当前月份，或使用显式固定日期）并忽略日期部分；或者可能使用间隔；或使用时间代表的秒数（即0-86399）。哪个合适取决于您将使用该值的用途。

看起来您可能正在对减去两个时间戳的结果进行子字符串化；在这种情况下，(a) 您已经有一个间隔，并且 (b) 您可能需要允许该差异跨越一天以上。您还可以直接从间隔值中提取各个时间分量。所以我会质疑你的做法是否真的合适。

Dates are stored in an internal representation and do not have any intrinsic format. What you are seeing is how your client is choosing to format the actual date value as a string for display, using your session's NLS_DATE_FORMAT setting (not always the case, but this is SQL Developer, so it is here.)

When you call to_date() with only the time components it defaults the date part to the first day of the current month, which is why you are seeing March 1st. That is a bit buried in the documentation:

If you specify a date value without a time component, then the default time is midnight. If you specify a date value without a date, then the default date is the first day of the current month.

But it is setting the time properly on that date, which you can see by either explicitly converting the date back to a string:

to_char(<your date>, 'YYYY-MM-DD HH24:MI:SS')

or by changing your session:

alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'

db<>fiddle

But you should not rely on NLS settings for anything except ad hoc queries; someone else running your code may have different settings. And only convert back to a string at the last moment when you have to display the value in a particular format - store it and pass it around as a native date.

Oracle doesn't have a time-only data type, so if you really only care about the time part then you can use a date (either defaulting to current month, or using an explicit fixed date) and ignore the date part; or potentially use an interval; or use the number of seconds the time represents (i.e. 0-86399). Which is suitable depends on what you'll use the value for.

It looks like you might be substringing the result of subtracting two timestamps; in which case (a) you already have an interval, and (b) you probably need to allow for that difference to span more than one day. You can also extract the individual time components directly from an interval value. So I'd question whether your approach is really appropriate.

回复收藏 0 原文

不知所踪 2025-01-24 01:13:54

您似乎正在计算 s.resolve_date-suspend_date，它为您提供 INTERVAL DAY TO SECOND 数据类型（意味着 s.resolve_date 之一或两者> 或 suspend_date 是 TIMESTAMP 数据类型），然后使用 SUBSTR 提取数据类型的时间部分，并尝试将其转换为日期然后显示时间部分。

不要这样做，因为使用 SUBSTR 很脆弱，因为它取决于 INTERVAL 的小数位数，而小数位数又取决于小数秒数TIMESTAMP 值具有。

只需选择一个日期并添加间隔，然后将其格式化为字符串即可显示：

SELECT s.resolve_date,
       suspend_date,
       TO_CHAR(
         DATE '1900-01-01' + (s.resolve_date-suspend_date),
         'hh24:mi:ss'
       ) sus_time
FROM   table_name s

对于示例数据：

CREATE TABLE table_name (resolve_date, suspend_date) AS
SELECT CAST(SYSTIMESTAMP AS TIMESTAMP(6)),
       CAST(TRUNC(SYSTIMESTAMP) AS TIMESTAMP(6))
FROM   DUAL;

输出：

<表类=“s-表”>
<标题>
RESOLVE_DATE
SUSPEND_DATE
SUS_TIME

<正文>
2022-03-25 12:59:15.223445
2022-03-25 00:00:00.000000
12:59:15

来自您的评论 :

我想做的就是格式化数据，以便在导出到 Excel 时可以正确排序。

这确实是一个 XY 问题。您可以在 Excel 中解决此问题，方法是在将数据导入 Excel 时将列格式指定为时间，或者右键单击列标题并“设置格式”列，选择具有正确格式模型的“时间”数据类型。

您还可以使用数字数据类型将时间输出为一天的一小部分，然后 Excel 可以使用以下任一方式将其格式化为正确的时间：

SELECT s.resolve_date,
       suspend_date,
       MOD(CAST(s.resolve_date AS DATE)-CAST(suspend_date AS DATE), 1) AS sus_time
FROM   table_name s;

或

SELECT s.resolve_date,
       suspend_date,
       TO_CHAR(DATE '1900-01-01' + (s.resolve_date-suspend_date), 'SSSSS')
         / 86400 AS sus_time
FROM   table_name s

两者都输出：

<表类=“s-表”>
<标题>
RESOLVE_DATE
SUSPEND_DATE
SUS_TIME

<正文>
2022-03-25 13:16:40.204461
2022-03-25 00:00:00.000000
.5532407407407407407407407407407407407407

这可能不是人类以该格式可读，但 Excel 会将其在时间列中重新格式化为 13:16:40。

db<>fiddle 此处

You appear to be calculating s.resolve_date-suspend_date which gives you an INTERVAL DAY TO SECOND data type (implying that one or both of s.resolve_date or suspend_date is a TIMESTAMP data type) and then using SUBSTR to extract the time component of the data type and trying to convert that to a date and then display the time component.

Don't do that as using SUBSTR is fragile as it depends on the number of decimal places that the INTERVAL has which, in turn, will depend on the number of fractional seconds that the TIMESTAMP values have.

Just pick a date and add the interval to it and then format it as string to display it:

SELECT s.resolve_date,
       suspend_date,
       TO_CHAR(
         DATE '1900-01-01' + (s.resolve_date-suspend_date),
         'hh24:mi:ss'
       ) sus_time
FROM   table_name s

Which, for the sample data:

CREATE TABLE table_name (resolve_date, suspend_date) AS
SELECT CAST(SYSTIMESTAMP AS TIMESTAMP(6)),
       CAST(TRUNC(SYSTIMESTAMP) AS TIMESTAMP(6))
FROM   DUAL;

Outputs:

RESOLVE_DATE SUSPEND_DATE SUS_TIME
2022-03-25 12:59:15.223445 2022-03-25 00:00:00.000000 12:59:15

RESOLVE_DATE	SUSPEND_DATE	SUS_TIME
2022-03-25 12:59:15.223445	2022-03-25 00:00:00.000000	12:59:15

From your comment:

all i'm trying to do is format the data so that when it's exported to Excel sorting works correctly.

That really is an XY-problem. You can solve it in Excel by either specifying the column format as a time when you import the data into Excel or right-click on the column header and "Format" the column picking the "time" data type with the correct format model.

You can also output the time as a fraction of a day with a numeric data type and then Excel can format it as the correct time using either:

SELECT s.resolve_date,
       suspend_date,
       MOD(CAST(s.resolve_date AS DATE)-CAST(suspend_date AS DATE), 1) AS sus_time
FROM   table_name s;

SELECT s.resolve_date,
       suspend_date,
       TO_CHAR(DATE '1900-01-01' + (s.resolve_date-suspend_date), 'SSSSS')
         / 86400 AS sus_time
FROM   table_name s

Which both output: