Extract month from number of days in a year-CodePudding

I am trying to extract month from number of day in year in Oracle SQL, for instance day 32 is february.

I do not have permission to use procedure, so I have to do it just using select.

Any help?

CodePudding user response：

You get to the solution as follows. First convert the day X into a date and extract the month from it again. Replace the value 150 with the day of the year you are looking for.

For Month as Number:

select extract(month from (to_date(150,'DDD'))) from dual;

Works also with the year. Format of the to-date must be simply changed to match.

select extract(month from (to_date('150 2022','DDD YYYY'))) from dual;

For Month as Month Name:

select to_char((to_date(150,'DDD')),'Month') from dual;

select to_char((to_date('150 2022','DDD YYYY')),'Month') from dual;

CodePudding user response：

It depends on a year; not all of them are equal (hint: leap years).

Here's the principle (based on year 2022); adjust it, if needed.

SQL> with test (datum) as
  2    (select date '2022-01-01'   level - 1
  3     from dual
  4     connect by level <= date '2023-01-01' - date '2022-01-01'
  5    )
  6  select to_char(datum, 'Mon') result
  7  from test
  8  where datum = date '2022-01-01'   &par_day;
Enter value for par_day: 32

RES
---
Feb

SQL> /
Enter value for par_day: 70

RES
---
Mar

SQL> /
Enter value for par_day: 352

RES
---
Dec

SQL>

CodePudding user response：

The day depends on the year, but if you want the current year, something like this should do:

select trunc(sysdate,'yy') 32 from dual;

(replace 32 with whatever you have)