How do I to mass update date and time?
-
Hi, I have to query an update for date and time. I know how to update the date alone, but I am having trouble with adding the time to the function. Right now, as it stands, it reads 4/20/2011 1:32:07 PM. I need the dock_date field to read 4/21/2011 7:00:00 AM. When I add 07:00:00 AM to the query I receive a DB error of: ORA-01830: date format picture ends before converting entire input string. Can someone please help me? Thank you, Justin I am joining two tables: cxadmin.ro_hist and cxadmin.ord_dtl and use the in function b/c I have to update multiple rows. My query so far is:
UPDATE cxadmin.ro_hist
SET DOCK_DATE = '20-APR-2011 7:00:00 AM'
where repair_ord in (
select recv_repair_ord from cxadmin.ord_dtl
where ord_nbr = '602945RR' AND DOCK_DATE > '20-APR-2011' ) -
Hi, I have to query an update for date and time. I know how to update the date alone, but I am having trouble with adding the time to the function. Right now, as it stands, it reads 4/20/2011 1:32:07 PM. I need the dock_date field to read 4/21/2011 7:00:00 AM. When I add 07:00:00 AM to the query I receive a DB error of: ORA-01830: date format picture ends before converting entire input string. Can someone please help me? Thank you, Justin I am joining two tables: cxadmin.ro_hist and cxadmin.ord_dtl and use the in function b/c I have to update multiple rows. My query so far is:
UPDATE cxadmin.ro_hist
SET DOCK_DATE = '20-APR-2011 7:00:00 AM'
where repair_ord in (
select recv_repair_ord from cxadmin.ord_dtl
where ord_nbr = '602945RR' AND DOCK_DATE > '20-APR-2011' )Have you tried to convert on date type? e.g
TO_DATE('20-APR-2011 7:00:00 AM', 'dd-MM-yyyy HH:MI:SS AM')
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.cacttus.com
-
Hi, I have to query an update for date and time. I know how to update the date alone, but I am having trouble with adding the time to the function. Right now, as it stands, it reads 4/20/2011 1:32:07 PM. I need the dock_date field to read 4/21/2011 7:00:00 AM. When I add 07:00:00 AM to the query I receive a DB error of: ORA-01830: date format picture ends before converting entire input string. Can someone please help me? Thank you, Justin I am joining two tables: cxadmin.ro_hist and cxadmin.ord_dtl and use the in function b/c I have to update multiple rows. My query so far is:
UPDATE cxadmin.ro_hist
SET DOCK_DATE = '20-APR-2011 7:00:00 AM'
where repair_ord in (
select recv_repair_ord from cxadmin.ord_dtl
where ord_nbr = '602945RR' AND DOCK_DATE > '20-APR-2011' )For SQL Server you might try:
SET DOC_DATE = CAST('20-APR-2011 7:00:00 AM' AS DATETIME)
or possibly:
SET DOC_DATE = '20-APR-2011 7:00:00' -- NO AM
-
Have you tried to convert on date type? e.g
TO_DATE('20-APR-2011 7:00:00 AM', 'dd-MM-yyyy HH:MI:SS AM')
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.cacttus.com
Thank you. It worked!
-
Thank you. It worked!
No problem :)
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.cacttus.com