CODEWITHSUNDEEP
oracle-database
Tpk43
Tpk43

Reputation: 331

Convert Numeric format dates to dates in Oracle

This is how my Date column looks like

RPT_DT (Data Type is Number)
20180131
20180130
20180129

I wanna extract month out of these dates(either Month or mm), and I tried below

select extract(month from to_date(Rpt_dt))
from
(
select distinct to_char(to_date(RPT_DT,'yyyymmdd'),'mm/dd/yyyy') Rpt_dt 
from  TABLE_NAME
)

I am getting the error "Not a valid month"

Upvotes: 0

Views: 108

Answers (3)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59642

This back-and-forth conversion is useless. Try this

select 
   extract(month from to_date(RPT_DT,'yyyymmdd'))
from  TABLE_NAME;

Upvotes: 0

Roberto Benazzato
Roberto Benazzato

Reputation: 467

if there is not any particular reason to have a double conversion I would suggest you to handle the problem with this simple query:

select substr(to_char(RPT_DT),5,2)from THE_TABLE

this query should be more performant since it make only one conversion. in your sample you transform:

  • a number to a date
  • then a date to a char
  • the char again in date
  • finally you extract the month

let me know if it help r.

Upvotes: 3

eifla001
eifla001

Reputation: 1157

try this,

SELECT EXTRACT(MONTH FROM TO_DATE(rpt_dt, 'YYYYMMDD')) 
  FROM TABLE_NAME;

and I believe you need to modify your query as you did not put the format 'MM/DD/YYYY',

select extract(month from to_date(Rpt_dt, 'MM/DD/YYYY'))
 from
 (
select distinct to_char(to_date(RPT_DT,'yyyymmdd'),'mm/dd/yyyy') Rpt_dt 
from  TABLE_NAME
 )

Upvotes: 1

Related Questions