Home » SQL & PL/SQL » SQL & PL/SQL » RAW datatype to sysdate (merged)
RAW datatype to sysdate (merged) [message #313727] Mon, 14 April 2008 06:14 Go to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi,

I wish to convert a string that has been stored in the format

YYYYMMDDHHMISS in RAW datatype field to the format MM/DD/YYYY HH:MI:SS AM.

Is this possible?

Could someone please help me out on this?

Thanks,

Sharath
Re: RAW datatype to sysdate [message #313729 is a reply to message #313727] Mon, 14 April 2008 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is NOT an expert question.

It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Rules are described in: OraFAQ Forum Guide
Read them, follow them.

As you are an expert, I don't answer the question because you obviously already knows it.

Regards
Michel

[Updated on: Mon, 14 April 2008 06:26]

Report message to a moderator

RAW datatype to sysdate [message #313736 is a reply to message #313727] Mon, 14 April 2008 06:37 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi,

I wish to convert a string that has been stored in the format
YYYYMMDDHHMISS in RAW datatype field to the format
MM/DD/YYYY HH:MI:SS AM.

Is this possible?

Could someone please help me out on this?

Thanks,

Shara
Re: RAW datatype to sysdate [message #313737 is a reply to message #313736] Mon, 14 April 2008 06:40 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Have a look at the UTL_RAW package.

MHE
Re: RAW datatype to sysdate [message #313951 is a reply to message #313737] Tue, 15 April 2008 05:35 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Maaher,

I only see use of a cast_to_varchar2 function.

However, when I do this, the data is not legible.

There are a couple of blocks followed by letters W and J.
I cannot even paste it here as it doesnt appear correctly.

Could you please advice?

SELECT UTL_RAW.CAST_TO_VARCHAR2(PVCA.SYS_UPDT_TIME) FROM POB_VENDOR_CLAIM_APPROVAL PVCA


Thanks,

Sharath
Re: RAW datatype to sysdate [message #313952 is a reply to message #313951] Tue, 15 April 2008 05:49 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
CAST_TO_NUMBER
Re: RAW datatype to sysdate [message #313966 is a reply to message #313952] Tue, 15 April 2008 06:25 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi,

This query is throwing ORA-6502 PL/SQL numeric or Value Error.

SELECT UTL_RAW.SUBSTR(PVCA.SYS_UPDT_TIME,1,14) FROM POB_VENDOR_CLAIM_APPROVAL PVCA

Could you please advice?

Sharath
Re: RAW datatype to date [message #313989 is a reply to message #313966] Tue, 15 April 2008 07:12 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi,

I have a raw column which is stored in the format

YYYYMMDDHHMISS and two additional characters which are random. So, for 20/3/2008 07:25:03 it is 20080320072503AK.

The approach I have decided on is to substr and remove 'AK' and convert the rest into number using CAST_TO_NUMBER function and then use the to_date function on it to receive the date.

Will this work?

This is the query,

SELECT TO_DATE(UTL_RAW.CAST_TO_NUMBER(UTL_RAW.SUBSTR(PVCA.SYS_UPDT_TIME,1,7)),'YYYYMMDDHHMISS') FROM POB_VENDOR_CLAIM_APPROVAL PVCA


However, this is throwing

ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Please help!

Sharath

[Updated on: Tue, 15 April 2008 07:12]

Report message to a moderator

Re: RAW datatype to sysdate (merged) [message #313990 is a reply to message #313727] Tue, 15 April 2008 07:26 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member

SQL> create table sam(dat raw(20));

Table created.

SQL> insert into sam values('20080415100505')

1 row created.

SQL> select dat,to_char(to_date(RAWTOHEX(dat),'YYYY-MM-DD    HH:MI:SS'),'MM/DD/YYYY HH:MI:SS AM') Result from sam;


DAT                                      Result
---------------------------------------- ----------------------
20080415100505                           04/15/2008 10:05:05 AM




Thanks

Mano
Re: RAW datatype to sysdate (merged) [message #313998 is a reply to message #313990] Tue, 15 April 2008 08:00 Go to previous message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Thank you so much Mano.

[Updated on: Tue, 15 April 2008 08:01]

Report message to a moderator

Previous Topic: Update Showing different results??
Next Topic: ORA-01747 (merged 2 threads)
Goto Forum:
  


Current Time: Wed Dec 07 02:45:11 CST 2016

Total time taken to generate the page: 0.07036 seconds