CODEWITHSUNDEEP
sqloracle-database
Manu
Manu

Reputation: 1

Oracle Data Conversion: ORA-01722: invalid number

Error while converting varchar2 column value to Number i am unable to compare varchar2 value field with another value like this '10:54' with '11.00', i have to compare minutes with minutes field. How is it possible?

Query is:

select 
adm_emp_num,adm_emp_nam,adm_ace_dte,asm_sft,asm_ed_tme,asm_st_tme,adm_ace_tme from Adm,asm
where adm_ace_dte =ass_sft_dte
and to_char(adm_ace_dte,'YYYYMM')=201409
--having min(to_char(adm_ace_tme,'HH24:MI')) < asm_st_tme
having min(to_char(adm_ace_tme,'HH24:MI')) < to_char(to_number(asm_st_tme,'99999
9.99'),'999999.99')
group by adm_emp_num,adm_emp_nam,adm_ace_dte,asm_sft,asm_ed_tme,asm_
st_tme,adm_ace_tme
order by 1,2

refer the table structures in the link: https://stackoverflow.com/review/suggested-edits/5861972

Any help will be appreciated Thanks

Upvotes: 0

Views: 460

Answers (3)

Rimas
Rimas

Reputation: 6024

Use EXTRATC function to get minute part. Examples:

  • from TIMESTAMP type:

    SELECT EXTRACT(MINUTE FROM SYSTIMESTAMP) FROM DUAL;

  • from DATE type:

    SELECT EXTRACT(MINUTE FROM CAST(SYSDATE AS TIMESTAMP)) FROM DUAL;

  • from VARCHAR2 type (for example: 11:30):

    SELECT EXTRACT(MINUTE FROM TO_TIMESTAMP('11:30', 'HH24:MI')) FROM DUAL;

Upvotes: 2

yamny
yamny

Reputation: 660

You can substring your value and convert it to number, for example:

...
WHERE TO_NUMBER(SUBSTR('10:54',4,2)) >= TO_NUMBER(SUBSTR('11:00',4,2));
--for minutes

...
WHERE TO_NUMBER(SUBSTR('10:54',1,2)) >= TO_NUMBER(SUBSTR('11:00',1,2));
--for hours

Upvotes: 0

davek
davek

Reputation: 22925

You can do it like this:

select to_number(to_char(to_date('12:56', 'hh24:mi'), 'mi')) from dual;

i.e. first convert your string to a valid date, then extract the minute part (by specifying just 'mi' in the format), then casting that to a number.

You can then perform comparisons:

...where to_number(to_char(to_date('12:56', 'hh24:mi'), 'mi')) = ...

You could of course just parse out the substring, but you would then be vulnerable to invalid minutes, like 79.

Upvotes: 0

Related Questions