update global condition 1

[hokky]

Hi guys,

I just need the query to update the datestamp, anything like %2050% becomes %1950%, but the date are still same.

Here's the example :
2050-10-01 00:00:00.000
becomes
1950-10-01 00:00:00.000

Thanks guys,

 

[SantaMufasa]

Hokky, How, precisely, are you "updating the timestamp"? Oracle certainly is able to distinguish between "1950..." and "2050...".

Once you post your UPDATE command(s), we can advise you how to make them align with the proper century.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[hokky]

I normally update one at a time using this command

Update from product 
set period='1950-10-01'
where period='2050-10-01'

Cheers,

 

[Dagon]

Does your update statement report that it has updated any rows ? Or does it say "no rows updated" ?

 

[jimirvine]

Hi
Is the period column a varchar2 or char datatype?

If it is, then change the table to store these values as Date datatypes.
If the period column is already a date datatype, then stop comparing the period column with character strings.

Update from product
set period='1950-10-01'
where period='2050-10-01'

Becomes

Update from product
set period=to_date('1950-10-01','yyyy-MM-dd')
where period=to_date('2050-10-01','yyyy-MM-dd')

Note** I have made an assumption about your date format that may be incorrect.

Where is your 'cutoff' i.e. when does a year in the 21st century start being the wrong century?

Jim

 

[hokky]

Guys,

my column is date type but the data is contain many date such

1950-10-01
1950-08-02
1950-07-07
1950-02-02
1950-01-01

And I want them becomes

2050-10-01
2050-08-02
2050-07-07
2050-02-02
2050-01-01

with one query, how do I do that?

 

[hoinz]

Hokky,

I think the problem is caused by the (well known but often forgotten) fact that date columns usually will hold time as well. Could you verify this for your columns?
Answering Dagon's question would have helped here. I bet no rows are updated, because of this very reason.
Also jimirvine's idea: where period=to_date('2050-10-01','yyyy-MM-dd')
will not update anything, because the time parts will never match.
If you want to update the year, but keep the rest of the date and time, you could try this:
(untested!)

Update product
set period=to_date('1950'||to_char(period,'MMDDHH24MISS'),'YYYYMMDDHH24MISS')
where to_char(period,'YYYY')='2050'

hope this helps

 

[jimirvine]

Also jimirvine's ... because the time parts will never match

are you sure about that? Bit of a general swingeing statement there! Since you have no idea how the dates were inserted, you have no idea as to the value of the time component of the dates, you therefore cannot make the assumption that you made. Granted, I also made an assumption that the date values had a truncated time element but I think that this is a far more valid assumption 1. Because of the code already supplied by the OP (using date strings without time elements)
and
2. because I'm biased

Check:

SQL> drop table dat;

Table dropped.

SQL>
SQL> create table dat (id number, dat1 date);

Table created.

SQL>
SQL> insert into dat values(1, to_date('10-Oct-1972','dd-mon-yyyy'));

1 row created.

SQL>--Notice the slightly different format model, watch out for that in the next bit.
SQL> insert into dat values(1, to_date('10-Oct-72','dd-mon-yy'));

1 row created.

SQL>
SQL> insert into dat values(1, trunc(sysdate));

1 row created.

SQL>
SQL> insert into dat values(1, sysdate);

1 row created.

SQL>
SQL> select * from dat;

                                                                        Page   1
        ID DAT1
---------- ---------
         1 10-OCT-72
         1 10-OCT-72
         1 15-JUN-07
         1 15-JUN-07

SQL>
SQL> select * from dat where dat1 = to_date('10-oct-1972','dd-mon-yyyy');

                                                                        Page   1
        ID DAT1
---------- ---------
         1 10-OCT-72

SQL>
SQL> select * from dat where dat1 = to_date('10-oct-1972','dd-mon-yyyy') or dat1 = trunc(sysdate);

                                                                        Page   1
        ID DAT1
---------- ---------
         1 10-OCT-72
         1 15-JUN-07

SQL>
SQL> select to_char(dat1,'dd-mon-yyyy hh:mm:ss') dat1 from dat ;

                                                                        Page   1
DAT1
--------------------
10-oct-1972 12:10:00
10-oct-2072 12:10:00
15-jun-2007 12:06:00
15-jun-2007 12:06:12

Clearly your statement is incorrect and in fact, under certain (fairly common) circumstances my predicate would indeed work perfectly well. I should, at this point, re-iterate the point that I was actually trying to make which is do NOT compare dates with strings (I wasn't actually considering the time component at that point, which maybe I should have)

Note that the OP actually points out that he wants dates with a century and year of 1950 to be altered to a century and year of 2050, whereas your update would in fact change '2050' century/year values to '1950' century/year values.

 

[jimirvine]

Hmmm, just noticed that the first couple of the times the op gives actual and expected data, it is a change from 2050 to 1950, then the final time it switches to 1950 to become 2050.
So Hokky, how about you give us EXACTLY what you really really need to be done

 

[hoinz]

jimirvine,

I agree that my assertion of your idea failing for the reasons I described was a bit overhasty. At least I should have waited for the OP to confirm or reject my ideas.
I apologize for that.

I understood your point that one cannot compare dates with strings.
My goal was to find an explanation why other solutions so far seemed not to work.
My point was the time part (that may or may not be there; I agree).

regards

 

[jimirvine]

Hoinz,
No need for apologies. If nothing else it allows for further clarification of a point.