我试图将其显示为显示，但是当输出应为＆＃x27;是＆＃x27;

发布于 2025-01-23 03:27:35 字数 442 浏览 3 评论 0原文

我正在尝试使其显示显示，但是当输出为“是”时，它使我无效

SELECT STRT_DT,END_DT,SYSDATE AS DT,
   CASE
    WHEN  TO_CHAR(STRT_DT,'MON-YYYY') <= TO_CHAR(SYSDATE,'MON-YYYY')
         AND TO_CHAR(END_DT,'MON-YYYY') = TO_CHAR(SYSDATE,'MON-YYYY') THEN 'Yes'
    END AS D
FROM
         (
            SELECT TO_DATE('14-JAN-2022','DD-MON-YYYY') AS STRT_DT
                 , TO_DATE('05-APR-2022','DD-MON-YYYY') AS END_DT
          FROM DUAL);

原文

I'm trying to get it to display but it is fetching me null when the output should be 'YES'

SELECT STRT_DT,END_DT,SYSDATE AS DT,
   CASE
    WHEN  TO_CHAR(STRT_DT,'MON-YYYY') <= TO_CHAR(SYSDATE,'MON-YYYY')
         AND TO_CHAR(END_DT,'MON-YYYY') = TO_CHAR(SYSDATE,'MON-YYYY') THEN 'Yes'
    END AS D
FROM
         (
            SELECT TO_DATE('14-JAN-2022','DD-MON-YYYY') AS STRT_DT
                 , TO_DATE('05-APR-2022','DD-MON-YYYY') AS END_DT
          FROM DUAL);

分享到QQ

分享到微博

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

发布评论

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

习惯那些不曾习惯的习惯 2025-01-30 03:27:35

由于这一行而无法使用：

TO_CHAR(STRT_DT,'MON-YYYY') <= TO_CHAR(SYSDATE,'MON-YYYY')

您在字符串上使用“比较运算符”，如果它是日期数据类型，则引擎会知道这两者之间的“更高”日期，但是由于它是字符串，因此此条件赢了' t按您预期的方式进行工作（它是按字母顺序比较这些字符串的方式），简单的解决方案就是将其替换为：

STRT_DT <= SYSDATE

或者，如果您只想比较一个月和年份：

TRUNC(STRT_DT,'MM') <= TRUNC(sysdate,'MM')

在这里我们截断了一个月，所以只有一个月和年份是相关的换句话说，如果strt_dt和sysdate在同一年和月份中，即使strt_dt是'reamight' （从该月的未来开始）然后sysdate。

It is not working because of this line:

TO_CHAR(STRT_DT,'MON-YYYY') <= TO_CHAR(SYSDATE,'MON-YYYY')

you are using 'Comparison Operator' on a string, if it is a date datatype the engine would know what is a 'higher' date between these two but because it is a string this condition won't work the way you intended (it is comparing those strings on alphabetical basis), simple solution would be to replace it just with:

STRT_DT <= SYSDATE

or if u want to compare only month and year:

TRUNC(STRT_DT,'MM') <= TRUNC(sysdate,'MM')

here we are truncating month so only month and year are relevant for comparison in other words if STRT_DT and SYSDATE are in the same year and month the condition will be satisfied even if the STRT_DT is 'higher' (day from the future of that month) then SYSDATE.

回复收藏 0 原文

执手闯天涯 2025-01-30 03:27:35

它无法正常工作；您正在比较一个月名称。切换到其他格式模型，该模型允许有意义的比较，例如yyyymm，而不是mon-yyyy：

SQL> SELECT STRT_DT,
  2         END_DT,
  3         SYSDATE AS DT,
  4         CASE
  5            WHEN     TO_CHAR (STRT_DT, 'yyyymm') <= TO_CHAR (SYSDATE, 'yyyymm')
  6                 AND TO_CHAR (END_DT, 'yyyymm') = TO_CHAR (SYSDATE, 'yyyymm')
  7            THEN
  8               'Yes'
  9            ELSE
 10               'No'
 11         END AS D
 12    FROM (SELECT TO_DATE ('14-JAN-2022', 'DD-MON-YYYY') AS STRT_DT,
 13                 TO_DATE ('05-APR-2022', 'DD-MON-YYYY') AS END_DT
 14            FROM DUAL);

STRT_DT    END_DT     DT         D
---------- ---------- ---------- ---
14.01.2022 05.04.2022 22.04.2022 Yes

SQL>

It can't work; you're comparing month names. Switch to a different format model which allows meaningful comparing, e.g. YYYYMM instead of MON-YYYY:

SQL> SELECT STRT_DT,
  2         END_DT,
  3         SYSDATE AS DT,
  4         CASE
  5            WHEN     TO_CHAR (STRT_DT, 'yyyymm') <= TO_CHAR (SYSDATE, 'yyyymm')
  6                 AND TO_CHAR (END_DT, 'yyyymm') = TO_CHAR (SYSDATE, 'yyyymm')
  7            THEN
  8               'Yes'
  9            ELSE
 10               'No'
 11         END AS D
 12    FROM (SELECT TO_DATE ('14-JAN-2022', 'DD-MON-YYYY') AS STRT_DT,
 13                 TO_DATE ('05-APR-2022', 'DD-MON-YYYY') AS END_DT
 14            FROM DUAL);

STRT_DT    END_DT     DT         D
---------- ---------- ---------- ---
14.01.2022 05.04.2022 22.04.2022 Yes

SQL>

回复收藏 0 原文

~没有更多了~