1

I have two table A and B where both have date fields but in different format('MM/DD/YYYY HH:MI:SS' and 'MM/DD/YYYY') and they both have a common field called invoiced_value. I need to update the date values of table A(with 'MM/DD/YYYY HH:MI:SS') from the values of table B(with 'MM/DD/YYYY'). Can i use the following query?. can i append the date like this to_date(e.invoiced_date ||' 3:56:24', 'MM/DD/YYYY HH:MI:SS') ?.

  UPDATE Table1
  SET m.invoiced_date = to_date(e.invoiced_date ||' 3:56:24', 'MM/DD/YYYY HH:MI:SS')
  FROM Table2 e, Table1 m
  WHERE m.invoiced_value = e.invoiced_value
TRomesh
  • 4,323
  • 8
  • 44
  • 74
  • 1
    Possible duplicate of [Oracle SQL: Update a table with data from another table](https://stackoverflow.com/questions/7030699/oracle-sql-update-a-table-with-data-from-another-table) –  Dec 20 '18 at 11:15
  • 1
    Your date format model isn't correct - how do you get times that are >= midday in? You need either `mm/dd/yyyy hh24:mi:ss` or `mm/dd/yyyy hh:mi:ss AM`, with the data passed in appropriately. – Boneist Dec 20 '18 at 11:43

1 Answers1

2

[TL;DR] Use a MERGE statement:

MERGE INTO Table1 dst
USING Table2 src
ON ( src.invoiced_value = dst.invoiced_value )
WHEN MATCHED THEN
  UPDATE SET invoiced_date = TRUNC( src.invoiced_date ) + INTERVAL '3:56:24' HOUR TO SECOND;

Can i use the following query?

No, an UPDATE statement does not have a FROM clause. You would need to use a correlated sub-query or a MERGE statement.

can i append the date like this to_date(e.invoiced_date ||' 3:56:24', 'MM/DD/YYYY HH:MI:SS') ?.

Maybe ... but you should not do it this way. TO_DATE( string_value, format_model ) takes a string as the first argument (and the || string concatenation operator also requires string arguments to concatenate) so your e.invoiced_date will be implicitly converted from a DATE to a string and your expression is effectively:

to_date(
  TO_CHAR(
    e.invoiced_date,
    ( SELECT value FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT' )
  ) || ' 3:56:24',
  'MM/DD/YYYY HH:MI:SS'
)

If your NLS_DATE_FORMAT session parameter is MM/DD/YYYY then your query will work. If it is something different then your query will either raise an exception or work but give incorrect results. Since NLS_DATE_FORMAT is a session parameter and each user can set it to whatever value they want then you should not rely on this to be consistent.

Instead, add an interval literal to the date (which does not require any conversions to-or-from a string):

TRUNC( src.invoiced_date ) + INTERVAL '3:56:24' HOUR TO SECOND

Or explicitly convert the date to a string in the correct format:

TO_DATE( TO_CHAR( e.invoiced_date, 'MM/DD/YYYY' ) || ' 3:56:24', 'MM/DD/YYYY HH24:MI:SS' )
MT0
  • 143,790
  • 11
  • 59
  • 117