将其转换为Oracle日期格式。

huangapple
go评论92阅读模式
英文:

convert into date format oracle

问题

I have a field c_days in the table my_table that accepts numeric values ​​from 1 to 31.
In this field, numbers from 1 to 9 are single digits.

I am writing a condition, if c_day is greater than today, then you need to display c_day in the to_date date format, if less, then display c_day in the date format, only the next month.

For example, c_day is 14, and today we have the 8th number, so you need to display the date 14.02.2023, If c_day is equal to 5, then you need to display the date of the next month 05.03.2023

I did something like this:

SELECT
C_DAY,
CASE
WHEN C_DAY >= TO_CHAR(SYSDATE, 'DD') THEN
TO_DATE(C_DAY || '.' || TO_CHAR(SYSDATE, 'MM') || '.' || TO_CHAR(SYSDATE, 'YYYY'), 'dd.mm.yyyy')
WHEN C_DAY < TO_CHAR(SYSDATE, 'DD') THEN
TO_DATE(C_DAY || '.' || TO_CHAR(SYSDATE, 'MM') || '.' || TO_CHAR(SYSDATE, 'YYYY'), 'dd.mm.yyyy')
WHEN C_DAY IS NULL THEN
null
END AS new_field
FROM my_table

The problem is that the end result is not converted to the date format, I thought it's cause of that the dates can be displayed as 1.03.2023, 7.03.2023, so I tried to convert it into

TO_CHAR(C_DAY, 'fm00')

and did this:

SELECT
C_DAY,
CASE
WHEN TO_CHAR(C_DAY, 'fm00') >= TO_CHAR(SYSDATE, 'DD') THEN
TO_DATE(TO_CHAR(C_DAY, 'fm00') || '.' || TO_CHAR(SYSDATE, 'MM') || '.' || TO_CHAR(SYSDATE, 'YYYY'), 'dd.mm.yyyy')
WHEN TO_CHAR(C_DAY, 'fm00') < TO_CHAR(SYSDATE, 'DD') THEN
TO_DATE(TO_CHAR(C_DAY, 'fm00') || '.' || TO_CHAR(SYSDATE, 'MM') || '.' || TO_CHAR(SYSDATE, 'YYYY'), 'dd.mm.yyyy')
WHEN C_DAY IS NULL THEN
null
END AS new_field
FROM my_table

But it's not working; it shows an ORA error.

英文:

I have a field c_days in the table my_table that accepts numeric values ​​from 1 to 31.
In this field, numbers from 1 to 9 are single digits.

I am writing a condition, if c_day is greater than today, then you need to display c_day in the to_date date format, if less, then display c_day in the date format, only the next month.

For example, c_day is 14, and today we have the 8th number, so you need to display the date 14.02.2023, If c_day is equal to 5, then you need to display the date of the next month 05.03.2023

I did something like this:

  1. SELECT 
  2.    C_DAY,
  3.    CASE
  4.      WHEN C_DAY &gt;= TO_CHAR(SYSDATE, &#39;DD&#39;) THEN 
  5.        TO_DATE(C_DAY || &#39;.&#39; || TO_CHAR(SYSDATE, &#39;MM&#39;) || &#39;.&#39; || TO_CHAR(SYSDATE, &#39;YYYY&#39;), &#39;dd.mm.yyyy&#39;)
  6.      WHEN C_DAY &lt; TO_CHAR(SYSDATE, &#39;DD&#39;) THEN
  7.        TO_DATE(C_DAY || &#39;.&#39; || TO_CHAR(SYSDATE, &#39;MM&#39;) || &#39;.&#39; || TO_CHAR(SYSDATE, &#39;YYYY&#39;), &#39;dd.mm.yyyy&#39;)
  8.      WHEN C_DAY IS NULL THEN
  9.        null
  10.    END AS new_field
  11. FROM my_table

The problem is that the end result is not converted to the date format, I thought it's cause of that the dates can be displayed as 1.03.2023, 7.03.2023, so i tried convert it into

  1. TO_CHAR(C_DAY, &#39;fm00&#39;)

and did this:

  1. SELECT 
  2.    C_DAY,
  3.    CASE
  4.      WHEN TO_CHAR(C_DAY, &#39;fm00&#39;) &gt;= TO_CHAR(SYSDATE, &#39;DD&#39;) THEN 
  5.        TO_DATE(TO_CHAR(C_DAY, &#39;fm00&#39;) || &#39;.&#39; || TO_CHAR(SYSDATE, &#39;MM&#39;) || &#39;.&#39; || TO_CHAR(SYSDATE, &#39;YYYY&#39;), &#39;dd.mm.yyyy&#39;)
  6.      WHEN TO_CHAR(C_DAY, &#39;fm00&#39;) &lt; TO_CHAR(SYSDATE, &#39;DD&#39;) THEN
  7.        TO_DATE(TO_CHAR(C_DAY, &#39;fm00&#39;) || &#39;.&#39; || TO_CHAR(SYSDATE, &#39;MM&#39;) || &#39;.&#39; || TO_CHAR(SYSDATE, &#39;YYYY&#39;), &#39;dd.mm.yyyy&#39;)
  8.      WHEN C_DAY IS NULL THEN
  9.        null
  10.    END AS new_field
  11. FROM my_table

But its not even working, it shows ora error

答案1

得分: 2

以下是代码部分的翻译：

  1. SELECT C_DAY,
  2.        LEAST(
  3.          ADD_MONTHS(
  4.            TRUNC(SYSDATE, 'MM'),
  5.            CASE WHEN c_day <= EXTRACT(DAY FROM SYSDATE) THEN 0 ELSE 1 END
  6.          ) + c_day - 1,
  7.          LAST_DAY(
  8.            ADD_MONTHS(
  9.              TRUNC(SYSDATE, 'MM'),
  10.              CASE WHEN c_day <= EXTRACT(DAY FROM SYSDATE) THEN 0 ELSE 1 END
  11.            )
  12.          )
  13.        ) AS new_field
  14. FROM   my_table

对于示例数据，输出如下：

  1. CREATE TABLE my_table(c_day) AS
  2.   SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 31;

输出结果如下：

C_DAY NEW_FIELD
1 2023-02-01 00:00:00
2 2023-02-02 00:00:00
3 2023-02-03 00:00:00
4 2023-02-04 00:00:00
5 2023-02-05 00:00:00
6 2023-02-06 00:00:00
7 2023-02-07 00:00:00
8 2023-02-08 00:00:00
9 2023-03-09 00:00:00
10 2023-03-10 00:00:00
11 2023-03-11 00:00:00
12 2023-03-12 00:00:00
13 2023-03-13 00:00:00
14 2023-03-14 00:00:00
15 2023-03-15 00:00:00
16 2023-03-16 00:00:00
17 2023-03-17 00:00:00
18 2023-03-18 00:00:00
19 2023-03-19 00:00:00
20 2023-03-20 00:00:00
21 2023-03-21 00:00:00
22 2023-03-22 00:00:00
23 2023-03-23 00:00:00
24 2023-03-24 00:00:00
25 2023-03-25 00:00:00
26 2023-03-26 00:00:00
27 2023-03-27 00:00:00
28 2023-03-28 00:00:00
29 2023-03-29 00:00:00
30 2023-03-30 00:00:00
31 2023-03-31 00:00:00 
  2. [fiddle](https://dbfiddle.uk/WAZeKvkO)
  4. <details>
  5. <summary>英文:</summary>
  7. You can do it without any string-to-date (or vice-versa) conversions using:
  9. ```lang-sql
  10. SELECT C_DAY,
  11.        LEAST(
  12.          ADD_MONTHS(
  13.            TRUNC(SYSDATE, &#39;MM&#39;),
  14.            CASE WHEN c_day &lt;= EXTRACT(DAY FROM SYSDATE) THEN 0 ELSE 1 END
  15.          ) + c_day - 1,
  16.          LAST_DAY(
  17.            ADD_MONTHS(
  18.              TRUNC(SYSDATE, &#39;MM&#39;),
  19.              CASE WHEN c_day &lt;= EXTRACT(DAY FROM SYSDATE) THEN 0 ELSE 1 END
  20.            )
  21.          )
  22.        ) AS new_field
  23. FROM   my_table

Which, for the sample data:

  1. CREATE TABLE my_table(c_day) AS
  2.   SELECT LEVEL FROM DUAL CONNECT BY LEVEL &lt;= 31;

Outputs:

C_DAY NEW_FIELD
1 2023-02-01 00:00:00
2 2023-02-02 00:00:00
3 2023-02-03 00:00:00
4 2023-02-04 00:00:00
5 2023-02-05 00:00:00
6 2023-02-06 00:00:00
7 2023-02-07 00:00:00
8 2023-02-08 00:00:00
9 2023-03-09 00:00:00
10 2023-03-10 00:00:00
11 2023-03-11 00:00:00
12 2023-03-12 00:00:00
13 2023-03-13 00:00:00
14 2023-03-14 00:00:00
15 2023-03-15 00:00:00
16 2023-03-16 00:00:00
17 2023-03-17 00:00:00
18 2023-03-18 00:00:00
19 2023-03-19 00:00:00
20 2023-03-20 00:00:00
21 2023-03-21 00:00:00
22 2023-03-22 00:00:00
23 2023-03-23 00:00:00
24 2023-03-24 00:00:00
25 2023-03-25 00:00:00
26 2023-03-26 00:00:00
27 2023-03-27 00:00:00
28 2023-03-28 00:00:00
29 2023-03-29 00:00:00
30 2023-03-30 00:00:00
31 2023-03-31 00:00:00

fiddle

答案2

得分: 1

以下是代码的翻译部分：

  1. SQL> alter session set nls_date_format = 'dd.mm.yyyy';
  3. Session altered.
  5. SQL> WITH
  6.   2     my_table (c_day)
  7.   3     AS
  8.   4        (SELECT 14 FROM DUAL
  9.   5         UNION ALL
  10.   6         SELECT 5 FROM DUAL
  11.   7         UNION ALL
  12.   8         SELECT 30 FROM DUAL)
  13.   9  SELECT c_day,
  14.  10         TO_DATE (
  15.  11               LPAD (LEAST (c_day, TO_CHAR (LAST_DAY (SYSDATE), 'dd')), 2, '0')
  16.  12            || '.'
  17.  13            || TO_CHAR (
  18.  14                  CASE
  19.  15                     WHEN c_day < TO_CHAR (SYSDATE, 'dd')
  20.  16                     THEN
  21.  17                        ADD_MONTHS (SYSDATE, 1)
  22.  18                     ELSE
  23.  19                        SYSDATE
  24.  20                  END,
  25.  21                  'mm.yyyy'),
  26.  22            'dd.mm.yyyy') AS result
  27.  23    FROM my_table;

希望这有所帮助！如果您有其他问题或需要进一步的帮助，请告诉我。

英文:

Here's one option:

  1. SQL&gt; alter session set nls_date_format = &#39;dd.mm.yyyy&#39;;
  3. Session altered.
  5. SQL&gt; WITH
  6.   2     my_table (c_day)
  7.   3     AS
  8.   4        (SELECT 14 FROM DUAL
  9.   5         UNION ALL
  10.   6         SELECT 5 FROM DUAL
  11.   7         UNION ALL
  12.   8         SELECT 30 FROM DUAL)
  13.   9  SELECT c_day,
  14.  10         TO_DATE (
  15.  11               LPAD (LEAST (c_day, TO_CHAR (LAST_DAY (SYSDATE), &#39;dd&#39;)), 2, &#39;0&#39;)
  16.  12            || &#39;.&#39;
  17.  13            || TO_CHAR (
  18.  14                  CASE
  19.  15                     WHEN c_day &lt; TO_CHAR (SYSDATE, &#39;dd&#39;)
  20.  16                     THEN
  21.  17                        ADD_MONTHS (SYSDATE, 1)
  22.  18                     ELSE
  23.  19                        SYSDATE
  24.  20                  END,
  25.  21                  &#39;mm.yyyy&#39;),
  26.  22            &#39;dd.mm.yyyy&#39;) AS result
  27.  23    FROM my_table;
  29.      C_DAY RESULT
  30. ---------- ----------
  31.         14 14.02.2023
  32.          5 05.03.2023
  33.         30 28.02.2023
  35. SQL&gt;

答案3

得分: 1

根据我理解你的问题，一个可能的解决方案如下：

  1. SELECT 
  2.    C_DAY,
  3.    CASE
  4.      WHEN C_DAY >= TO_CHAR(SYSDATE, 'DD') THEN 
  5.        ADD_MONTHS(TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-')||C_DAY , 'YYYY-MM-DD'), 1)
  6.      WHEN C_DAY < TO_CHAR(SYSDATE, 'DD') THEN
  7.        TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-')||C_DAY , 'YYYY-MM-DD')
  8.    END AS new_field
  9. FROM my_table

ADD_MONTHS 函数的工作原理如下：

如果 date 是当月的最后一天，或者结果月份的天数少于 date 的日期部分，那么结果是结果月份的最后一天。否则，结果具有与 date 相同的日期部分。

英文:

As far as I understand your question, one solution could be this one

  1. SELECT 
  2.    C_DAY,
  3.    CASE
  4.      WHEN C_DAY &gt;= TO_CHAR(SYSDATE, &#39;DD&#39;) THEN 
  5.        ADD_MONTHS(TO_DATE(TO_CHAR(SYSDATE, &#39;YYYY-MM-&#39;)||C_DAY , &#39;YYYY-MM-DD&#39;), 1)
  6.      WHEN C_DAY &lt; TO_CHAR(SYSDATE, &#39;DD&#39;) THEN
  7.        TO_DATE(TO_CHAR(SYSDATE, &#39;YYYY-MM-&#39;)||C_DAY , &#39;YYYY-MM-DD&#39;)
  8.    END AS new_field
  9. FROM my_table

The ADD_MONTHS function works as this:
>If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as date.

huangapple
  • 本文由 发表于 2023年2月8日 14:59:01
  • 转载请务必保留本文链接：https://go.coder-hub.com/75382316.html
  • oracle
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定