CODEWITHSUNDEEP
oracle-databaseoracle11gtoad
Sid M
Sid M

Reputation: 4354

Select Month in Oracle Query

I tried the below query in oracle

select cast(TO_DATE (cal.MONTH,'MM') AS varchar2(30)) as result 
FROM  JOBCONTROL_USER.ods_calendar_weeks cal  
WHERE cal.YEAR NOT IN (0, 9999)

it gives result in dd-mon-yy format. Now I want only mon from the result, how can I achieve this without using to_char()?

Upvotes: 1

Views: 765

Answers (3)

Smart003
Smart003

Reputation: 1119

select to_char(cal.Month,'month')
              ) AS result
FROM  JOBCONTROL_USER.ods_calendar_weeks cal  
WHERE cal.YEAR NOT IN (0, 9999);

This will gives month. the to_char() is a function which has two arguments 1. Column name ans 2. Month. Column name is of date data type so we have to convert the date into character data type and we required only the month so the second column will describes what will be extracted from the date. Finally the result is displayed as a character datatype. This query will returns the months name if year neither 0 nor 9999.

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191235

If you're avoiding Oracle functions and the month number is stored on its own as a varchar2 field, then you could brute-force it:

select case cast(month as number)
    when 1 then 'Jan'
    when 2 then 'Feb'
    when 3 then 'Mar'
    when 4 then 'Apr'
    when 5 then 'May'
    when 6 then 'Jun'
    when 7 then 'Jul'
    when 8 then 'Aug'
    when 9 then 'Sep'
    when 10 then 'Oct'
    when 11 then 'Nov'
    when 12 then 'Dec'
  end as mon
from ods_calendar_weeks cal
where cal.year not in (0, 9999);

But you're having to specify the language; you don't get Oracle's conversion of the month to the NLS date language. Which might be a bonus or a problem depending on your context.

I'd be tempted to put the conversions into a look-up table instead and join to that; or to add the month name as a separate column on the table, as a virtual column in 11g.

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

You can try somthing like this:-

SELECT EXTRACT(MONTH FROM CAST(TO_DATE (cal.MONTH,'MM') AS varchar2(30))) as RESULT 
FROM  JOBCONTROL_USER.ods_calendar_weeks cal  
WHERE cal.YEAR NOT IN (0, 9999)

Hope this will help you.

Upvotes: 0

Related Questions