如何处理日期格式 [英] how to handle date format
问题描述
大家早上好..
i试图通过使用如下语法的日期比较来更新表格。
update table_name set employee_salary = 100000其中emp_id ='121'
和to_char(to_date(JOIN_DATE,'DD-MON-YY HH24:MI:SS'),'YYYY-MM -DD HH24:MI:SS')< to_char(TO_DATE(V_JOIN_DATE,'YYYY-MM-DD HH24:MI:SS:SSSSS'),'YYYY-MM-DD HH24:MI:SS');
表格中的上述查询join_date是DATE类型,V_JOIN_DATE是varchar2(50)。
上面的查询我得到以下错误。
hi all good morning..
i have tried to update a table by using date comparison like below syntax.
update table_name set employee_salary=100000 where emp_id='121'
and to_char(to_date(JOIN_DATE,'DD-MON-YY HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') < to_char(TO_DATE(V_JOIN_DATE,'YYYY-MM-DD HH24:MI:SS:SSSSS'),'YYYY-MM-DD HH24:MI:SS');
in the above query join_date in the table is DATE type and V_JOIN_DATE is varchar2(50).
for the above query i am getting below error.
ORA-01836: hour conflicts with seconds in day
01836. 00000 - "hour conflicts with seconds in day"
*Cause:
*Action:
i试图通过以下查询选择日期
SELECT TO_DATE ('2013-09-18 05:32:54:00000','YYYY-MM-DD HH24:MI:SS')来自双重;
但这里同样低于错误。
i have tried to select date by using below query
SELECT TO_DATE('2013-09-18 05:32:54:00000','YYYY-MM-DD HH24:MI:SS') FROM DUAL;
but here also getting same below error.
ORA-01830: date format picture ends before converting entire input string
01830. 00000 - "date format picture ends before converting entire input string"
*Cause:
*Action:
但是我需要比较join_date到2013-09-18 05:32:54。
是否有任何解决方案可以解决上述错误并获得提及的日期格式。
but i need compare join_date upto 2013-09-18 05:32:54.
is there any solution to solve the above errors and to get mentioned date format.
推荐答案
Oracle仅将分数存储到秒在日期字段中。
使用TIMESTAMP:
Oracle stores only the fractions up to second in a DATE field.
Use TIMESTAMP instead:
SELECT TO_TIMESTAMP('2013-09-18 05:32:54:00000','YYYY-MM-DD HH24:MI:SS,FF') D FROM DUAL;
这篇关于如何处理日期格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!