英文:
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:
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 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 its not even working, it shows ora error
答案1
得分: 2
以下是代码部分的翻译:
SELECT C_DAY,
LEAST(
ADD_MONTHS(
TRUNC(SYSDATE, 'MM'),
CASE WHEN c_day <= EXTRACT(DAY FROM SYSDATE) THEN 0 ELSE 1 END
) + c_day - 1,
LAST_DAY(
ADD_MONTHS(
TRUNC(SYSDATE, 'MM'),
CASE WHEN c_day <= EXTRACT(DAY FROM SYSDATE) THEN 0 ELSE 1 END
)
)
) AS new_field
FROM my_table
对于示例数据,输出如下:
CREATE TABLE my_table(c_day) AS
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 |
[fiddle](https://dbfiddle.uk/WAZeKvkO)
<details>
<summary>英文:</summary>
You can do it without any string-to-date (or vice-versa) conversions using:
```lang-sql
SELECT C_DAY,
LEAST(
ADD_MONTHS(
TRUNC(SYSDATE, 'MM'),
CASE WHEN c_day <= EXTRACT(DAY FROM SYSDATE) THEN 0 ELSE 1 END
) + c_day - 1,
LAST_DAY(
ADD_MONTHS(
TRUNC(SYSDATE, 'MM'),
CASE WHEN c_day <= EXTRACT(DAY FROM SYSDATE) THEN 0 ELSE 1 END
)
)
) AS new_field
FROM my_table
Which, for the sample data:
CREATE TABLE my_table(c_day) AS
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 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 |
答案2
得分: 1
以下是代码的翻译部分:
SQL> alter session set nls_date_format = 'dd.mm.yyyy';
Session altered.
SQL> WITH
2 my_table (c_day)
3 AS
4 (SELECT 14 FROM DUAL
5 UNION ALL
6 SELECT 5 FROM DUAL
7 UNION ALL
8 SELECT 30 FROM DUAL)
9 SELECT c_day,
10 TO_DATE (
11 LPAD (LEAST (c_day, TO_CHAR (LAST_DAY (SYSDATE), 'dd')), 2, '0')
12 || '.'
13 || TO_CHAR (
14 CASE
15 WHEN c_day < TO_CHAR (SYSDATE, 'dd')
16 THEN
17 ADD_MONTHS (SYSDATE, 1)
18 ELSE
19 SYSDATE
20 END,
21 'mm.yyyy'),
22 'dd.mm.yyyy') AS result
23 FROM my_table;
希望这有所帮助!如果您有其他问题或需要进一步的帮助,请告诉我。
英文:
Here's one option:
SQL> alter session set nls_date_format = 'dd.mm.yyyy';
Session altered.
SQL> WITH
2 my_table (c_day)
3 AS
4 (SELECT 14 FROM DUAL
5 UNION ALL
6 SELECT 5 FROM DUAL
7 UNION ALL
8 SELECT 30 FROM DUAL)
9 SELECT c_day,
10 TO_DATE (
11 LPAD (LEAST (c_day, TO_CHAR (LAST_DAY (SYSDATE), 'dd')), 2, '0')
12 || '.'
13 || TO_CHAR (
14 CASE
15 WHEN c_day < TO_CHAR (SYSDATE, 'dd')
16 THEN
17 ADD_MONTHS (SYSDATE, 1)
18 ELSE
19 SYSDATE
20 END,
21 'mm.yyyy'),
22 'dd.mm.yyyy') AS result
23 FROM my_table;
C_DAY RESULT
---------- ----------
14 14.02.2023
5 05.03.2023
30 28.02.2023
SQL>
答案3
得分: 1
根据我理解你的问题,一个可能的解决方案如下:
SELECT
C_DAY,
CASE
WHEN C_DAY >= TO_CHAR(SYSDATE, 'DD') THEN
ADD_MONTHS(TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-')||C_DAY , 'YYYY-MM-DD'), 1)
WHEN C_DAY < TO_CHAR(SYSDATE, 'DD') THEN
TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-')||C_DAY , 'YYYY-MM-DD')
END AS new_field
FROM my_table
ADD_MONTHS
函数的工作原理如下:
如果
date
是当月的最后一天,或者结果月份的天数少于date
的日期部分,那么结果是结果月份的最后一天。否则,结果具有与date
相同的日期部分。
英文:
As far as I understand your question, one solution could be this one
SELECT
C_DAY,
CASE
WHEN C_DAY >= TO_CHAR(SYSDATE, 'DD') THEN
ADD_MONTHS(TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-')||C_DAY , 'YYYY-MM-DD'), 1)
WHEN C_DAY < TO_CHAR(SYSDATE, 'DD') THEN
TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-')||C_DAY , 'YYYY-MM-DD')
END AS new_field
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
.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论