CODEWITHSUNDEEP
sqloracle-database
Pavel Trostianko
Pavel Trostianko

Reputation: 119

How convert varchar with quarter date to date format?

I have varchar2 (7 BYTE) PERIOD column with year and quarter number in format like this:

2017-03

I need to convert it to date format, but than i try this:

SELECT TO_CHAR(PERIOD, 'YYYY-MM') FROM SCHEM_NAME.TABLE_NAME

I get error:

ORA-01722: invalid number 01722. 00000 - "invalid number" *Cause: The specified number was invalid. *Action: Specify a valid number.

I understand that specified number is invalid, but I didn't understand that format I need to try.

Also after I will try to drop all data from table that quarter older than five years from todays date.

Can you explain what is the most appropriate or best way to do this?

I want to use DROP PARTITION something like:

ALTER TABLE SCHEM_NAME.TABLE_NAME DROP PARTITION

UPDATE:

Using this:

SELECT TO_DATE(MAX(PERIOD), 'YYYY-MM') - interval '5' year
FROM SCHEM_NAME.TABLE_NAME

I get date from that I need to make drop. As example:

01-MAR-17

I need to drop everything that is older than this date. What is the most appropriate way to do this?

Upvotes: 1

Views: 298

Answers (1)

flyaround
flyaround

Reputation: 363

Select:

As asked, your PERIOD column contains the quarter of a year in varchar2.

SELECT * FROM SCHEM_NAME.TABLE_NAME 
WHERE LAST_DAY(ADD_MONTHS(TO_DATE(SUBSTR(PERIOD, 1, Instr(PERIOD, '-', -1, 1) -1) 
  || '-' || 
  LPAD(TO_NUMBER(SUBSTR(PERIOD, Instr(PERIOD, '-', -1, 1) +1))*3, 2, 0), 'YYYY-MM'), (5*12))) > SYSDATE;

Delete:

DELETE SCHEM_NAME.TABLE_NAME 
WHERE LAST_DAY(ADD_MONTHS(TO_DATE(SUBSTR(PERIOD, 1, Instr(PERIOD, '-', -1, 1) -1) 
  || '-' || 
  LPAD(TO_NUMBER(SUBSTR(PERIOD, Instr(PERIOD, '-', -1, 1) +1))*3, 2, 0), 'YYYY-MM'), (5*12))) > SYSDATE;

Explanation:

  • With Substr, Instr the year and quarter will be extracted from the varchar2 PERIOD.
  • LAST_DAY to use the last day of the multiplied month of quarter.
  • ADD_MONTHS to add 5 years to PERIOD for the condition related to current date (SYSDATE).

Upvotes: 1

Related Questions