将varchar2列值转换为Number时出错我无法将varchar2值字段与另一个值进行比较像这个"10:54"one_answers"11.00",我必须比较分钟和分钟字段。这怎么可能?
查询为:
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
请参阅链接中的表结构:https://stackoverflow.com/review/suggested-edits/5861972
任何帮助都将不胜感激感谢
使用EXTRATC
函数获取微小部分。示例:
-
来自
TIMESTAMP
类型:SELECT EXTRACT(MINUTE FROM SYSTIMESTAMP) FROM DUAL;
-
来自
DATE
类型:SELECT EXTRACT(MINUTE FROM CAST(SYSDATE AS TIMESTAMP)) FROM DUAL;
-
从
VARCHAR2
类型(例如:11:30
):SELECT EXTRACT(MINUTE FROM TO_TIMESTAMP('11:30', 'HH24:MI')) FROM DUAL;
你可以这样做:
select to_number(to_char(to_date('12:56', 'hh24:mi'), 'mi')) from dual;
即,首先将字符串转换为有效的date
,然后提取minute
部分(通过在格式中仅指定'mi'
),然后将其强制转换为数字。
然后您可以执行比较:
...where to_number(to_char(to_date('12:56', 'hh24:mi'), 'mi')) = ...
当然,您可以解析出子字符串,但您可能会受到无效分钟的攻击,比如79分钟。
您可以用子字符串将值转换为数字,例如:
...
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