OraFAQ Forum: SQL & PL/SQL » update century in timestamp column in table (topics merged)

Home » SQL & PL/SQL » SQL & PL/SQL » update century in timestamp column in table (topics merged) (oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

update century in timestamp column in table (topics merged) [message #640629]

Sat, 01 August 2015 12:27

kadhirvel.k
Messages: 10
Registered: August 2015
Location: chennai

Junior Member

Hi Guys..

I have a table with a time stamp column updated by a procedure. as there was a bug the century field not stored. it shows 0013 instead of 2013. The table has thousands of records in production since 2003.but no century field. Could u give me script for updating only the century part in the timestamp column.

Report message to a moderator

Re: update century in timestamp column in table [message #640630 is a reply to message #640629]

Sat, 01 August 2015 12:36

Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA

Senior Member

Assuming you need to update date in all rows:

update tbl
  set dt = add_months(dt,24000)
/

Otherwise add where clause to limit update to desired rows.

SY.

Report message to a moderator

Re: update century in timestamp column in table [message #640631 is a reply to message #640630]

Sat, 01 August 2015 12:43

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator

Re: update century in timestamp column in table [message #640632 is a reply to message #640630]

Sat, 01 August 2015 13:27

Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA

Senior Member

Actually I missed you have timestamp, not date column, so add_months(dt,24000) will result in losing fractional part of second. If column data type is TIMESTAMP use:

update tbl
  set ts = ts + interval '2000' year(4)
/

SY.

Report message to a moderator

Re: update century in timestamp column in table [message #640638 is a reply to message #640632]

Sun, 02 August 2015 08:40

kadhirvel.k
Messages: 10
Registered: August 2015
Location: chennai

Junior Member

thank you. will it work on 29-Feb on leap year?

Report message to a moderator

Re: update century in timestamp column in table [message #640640 is a reply to message #640638]

Sun, 02 August 2015 09:20

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

kadhirvel.k wrote on Sun, 02 August 2015 06:40

thank you. will it work on 29-Feb on leap year?

YES

V10 is obsoleted & unsupport.
It should be upgraded to a supported version ASAP

Report message to a moderator

Re: update century in timestamp column in table [message #640643 is a reply to message #640638]

Sun, 02 August 2015 10:48

Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA

Senior Member

kadhirvel.k wrote on Sun, 02 August 2015 09:40

thank you. will it work on 29-Feb on leap year?

Why don't you try it?

SY.

Report message to a moderator

oracle timestamp Conversion [message #640714 is a reply to message #640629]

Tue, 04 August 2015 06:46

kadhirvel.k
Messages: 10
Registered: August 2015
Location: chennai

Junior Member

Hi Guys,

a bug reported where century field storing as 0015 instead of 2015 in table. we are using following code to update the business datetime. Here M_business_date is date variable.my NLS timestamp format is DD-MON-RR HH.MI.SSXFF AM. Kindly Help.


TO_TIMESTAMP(M_BUSINESS_DATE || ' ' ||
                                                TO_CHAR(SYSDATE,
                                                        'HH24:MI:SS'),
                                                'dd-mm-yyyy hh24:mi:ss.ff')

Report message to a moderator

Re: oracle timestamp Conversion [message #640715 is a reply to message #640714]

Tue, 04 August 2015 07:00

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

Why are you pulling time from sysdate?
Why not just use Solomon's last suggestion?

Report message to a moderator

Re: oracle timestamp Conversion [message #640765 is a reply to message #640715]

Tue, 04 August 2015 20:55

kadhirvel.k
Messages: 10
Registered: August 2015
Location: chennai

Junior Member

it was the business requirement to take the time part from sysdate

Report message to a moderator

Re: oracle timestamp Conversion [message #640766 is a reply to message #640714]

Tue, 04 August 2015 21:08

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

kadhirvel.k wrote on Tue, 04 August 2015 04:46

Hi Guys,

a bug reported where century field storing as 0015 instead of 2015 in table. we are using following code to update the business datetime. Here M_business_date is date variable.my NLS timestamp format is DD-MON-RR HH.MI.SSXFF AM. Kindly Help.
TO_TIMESTAMP(M_BUSINESS_DATE || ' ' ||
                                                TO_CHAR(SYSDATE,
                                                        'HH24:MI:SS'),
                                                'dd-mm-yyyy hh24:mi:ss.ff')

SYSDATE does NOT contain fractions of a second (".ff") in the TO_CHAR mask

Report message to a moderator

Re: oracle timestamp Conversion [message #640767 is a reply to message #640766]

Tue, 04 August 2015 23:23

kadhirvel.k
Messages: 10
Registered: August 2015
Location: chennai

Junior Member

it will take default 00000. kindly answer my question why century field storing as 0015 instead of 2015 in table

Report message to a moderator

Re: oracle timestamp Conversion [message #640768 is a reply to message #640767]

Tue, 04 August 2015 23:31

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

kadhirvel.k wrote on Tue, 04 August 2015 21:23

it will take default 00000. kindly answer my question why century field storing as 0015 instead of 2015 in table

results depend upon M_BUSINESS_DATE & I have NO idea what it contains.

Report message to a moderator

Re: oracle timestamp Conversion [message #640769 is a reply to message #640768]

Tue, 04 August 2015 23:34

kadhirvel.k
Messages: 10
Registered: August 2015
Location: chennai

Junior Member

it is a date variable (DD-MM-RR) contains business date

Report message to a moderator

Re: oracle timestamp Conversion [message #640770 is a reply to message #640769]

Tue, 04 August 2015 23:38

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

kadhirvel.k wrote on Tue, 04 August 2015 21:34

it is a date variable (DD-MM-RR) contains business date

why RR?
Did you sleep through Y2K?
You reap what you sowed.

Report message to a moderator

Re: oracle timestamp Conversion [message #640786 is a reply to message #640770]

Wed, 05 August 2015 03:02

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

Dates are not stored in any given format. Read this:
http://edstevensdba.wordpress.com/2011/04/07/nls_date_format/

Then start using format masks and to_date and to_timestamp properly instead of relying on implicit conversion.
I suspect your default timestamp format contains YYYY

Report message to a moderator

Re: oracle timestamp Conversion [message #640807 is a reply to message #640769]

Wed, 05 August 2015 06:47

EdStevens
Messages: 1377
Registered: September 2013

Senior Member

kadhirvel.k wrote on Tue, 04 August 2015 23:34

it is a date variable (DD-MM-RR) contains business date

The RR format mask was intended as a TEMPORARY expedient to help system get through Y2k. It should no longer be used. It is assumed (apparently improperly) that the industry had learned its lesson and people would start using explicit 4-digit years. Unfortunately, an increasing number of people in the industry weren't even potty trained during y2k and the institutional memory is being lost. And of course, those of us who DO remember because we were there are increasingly being dismissed as dinosaurs whose knowledge and experience is considered 'irrelevent'.

Bottom line is, the reason you have '00' for the century in your dates is because you (the application code in your organization) mis-handled dates and inserted it that way.

Report message to a moderator

Re: oracle timestamp Conversion [message #640812 is a reply to message #640807]

Wed, 05 August 2015 08:10

kadhirvel.k
Messages: 10
Registered: August 2015
Location: chennai

Junior Member

Thanks Sir.. one last question

TO_TIMESTAMP(M_BUSINESS_DATE || ' ' ||
                                                TO_CHAR(SYSDATE,
                                                        'HH24:MI:SS'),
                                                'dd-mm-yyyy hh24:mi:ss.ff')

If we update the table timestamp column in the above mentioned way, not all year field are storing as 0015. But some 10% storing correctly as 2015. any specific reason for this.

Report message to a moderator

Re: oracle timestamp Conversion [message #640813 is a reply to message #640812]

Wed, 05 August 2015 08:16

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Oracle only does what it is told to do.

https://en.wikipedia.org/wiki/Garbage_in,_garbage_out

Report message to a moderator

Re: oracle timestamp Conversion [message #640814 is a reply to message #640812]

Wed, 05 August 2015 08:16

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

Probably, if you fill in all the blanks we may even be able to tell you.
Blanks are:
the full update statement used
your systems default date format
your systems default timestamp format

Report message to a moderator

Re: oracle timestamp Conversion [message #640821 is a reply to message #640814]

Wed, 05 August 2015 08:25

kadhirvel.k
Messages: 10
Registered: August 2015
Location: chennai

Junior Member

UPDATE TV_FINAL_DECISION F
         SET F.DT_REJECTION_DATE = TO_TIMESTAMP(M_BUSINESS_DATE || ' ' ||TO_CHAR(SYSDATE,'HH24:MI:SS'),'dd-mm-yyyy hh24:mi:ss.ff');

M_BUSINESS_DATE is of variable DATE := FUN_GET_BUSINESSDATE

NLS_DATE_FORMAT DD-MON-RR
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

we update the table timestamp column in the above mentioned way, not all year field are storing as 0015. But some 10% storing correctly as 2015. any specific reason for this.

Report message to a moderator

Re: oracle timestamp Conversion [message #640822 is a reply to message #640821]

Wed, 05 August 2015 08:34

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

Give a specific value for m_business_date that's getting set correctly.

Report message to a moderator

Re: oracle timestamp Conversion [message #640823 is a reply to message #640822]

Wed, 05 August 2015 08:38

kadhirvel.k
Messages: 10
Registered: August 2015
Location: chennai

Junior Member

select FUN_GET_BUSINESSDATE('001') || ' ' || TO_CHAR(SYSDATE, 'HH24:MI:SS') from dual;

the above one gives me the following output

20-SEP-15 19:01:29

Report message to a moderator

Re: oracle timestamp Conversion [message #640825 is a reply to message #640823]

Wed, 05 August 2015 08:43

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

And you know that's 2015 rather than 0015 (or 0115 or 15015 or ....) how?
STOP USING 2 DIGIT YEARS!

Report message to a moderator

Re: oracle timestamp Conversion [message #640826 is a reply to message #640825]

Wed, 05 August 2015 09:14

Bill B
Messages: 1971
Registered: December 2004

Senior Member

He also said that not ALL the rows need to be updated. Make sure you use a where clause on those updates to only update dates less then january 1, 1000

Report message to a moderator

Previous Topic:	sql replacement price
Next Topic:	Need modification in Stored Procedure.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 29 18:19:44 CDT 2026