This content has been marked as final. Show 7 replies
set op_date = to_date(to_char(op_date,'dd/mm' ) ||'/'|| to_char(cl_date,'yyyy'),'dd/mm/yyyy')
This should work
UPDATE Audit SET Op_date = TO_DATE(TO_CHAR(Op_date,'DD-MON') || '-' || TO_CHAR(CL_date,'YYYY'))
Check this as well.
UPDATE audit SET op_date = ADD_MONTHS ( op_date, TRUNC ( MONTHS_BETWEEN (TRUNC (cl_Date, 'YYYY'), TRUNC (op_Date, 'YYYY'))));
You have the answer to your technical problem, but please see my embedded comment, to clarify your understanding of how dates are handled
user10069916 wrote:Your inclusion of a 'format' with a data element that is declared as a DATE is irrelevant and indicative of a misconception on your part. DATEs do NOT have a format -- at least not one that humans would recognize or care about. If you declare an element to be a DATE, oracle will use its own consistent and internal format to deal with it. Your mention of a format of 'dd/mm/yyyy' is only how you choose to display that date as a character string. You need to keep that concept firmly in mind whenever you deal with dates. A date is an abstract concept, which is represented by a string of characters. The particular characters used and their sequence can vary, as long as we all agree on what each character means, but that does not change the underlying abstract date.
Table Name : Audit
ID Op_date Cl_Date
1 12/02/2014 15/02/2012
2 4/01/2071 16/12/2012
Op_date datatype date (dd/mm/yyyy)
cl_date datatype date (dd/mm/yyyy)
Please help me to update op_date's 'YYYY; value in to CL_date 'YYYY' value
final resulet would be
ID Op_date Cl_Date
1 12/02/2012 15/02/2012
2 4/01/2012 16/12/2012
Please help me.
Beware proposals transferring YYYY
Table Name : Audit ID | Op_date | Cl_Date 1 | 29/02/2016 | 15/02/2012 /* last year you were safe */ 2 | 29/02/2016 | 16/02/2013 /* this year you're not */