CODEWITHSUNDEEP
sqloracle-database
Andrey Romanov
Andrey Romanov

Reputation: 69

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 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

Upvotes: 0

Views: 115

Answers (3)

MT0
MT0

Reputation: 167784

You can do it without any string-to-date (or vice-versa) conversions using:

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

fiddle

Upvotes: 2

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

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.

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142720

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>

Upvotes: 1

Related Questions