CODEWITHSUNDEEP
sqloracle-database
Dinny
Dinny

Reputation: 5

Extract month from number of days in a year

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?

Upvotes: 0

Views: 133

Answers (3)

gsalem
gsalem

Reputation: 2028

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 number of days you have MINUS 1)

Upvotes: 0

Florian
Florian

Reputation: 75

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;

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 142705

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>

Upvotes: 0

Related Questions