OraFAQ Forum: SQL & PL/SQL => Convert string to date format

Members

Help

Home

Home » SQL & PL/SQL » SQL & PL/SQL » Convert string to date format

Show: Today's Messages :: Show Polls :: Message Navigator

E-mail to friend

Convert string to date format [message #319744]

Mon, 12 May 2008 22:17

iriswancy
Messages: 15
Registered: December 2005
Location: Malaysia

Junior Member

Hi,

I need help on this :

This is the setting in the Oracle database
NLS_DATE_FORMAT = dd-Mon-yyyy

DESC t_exam_settings : FEE_INFO VARCHAR2(500)
**values in fee_info : 10/04/2007;PBB310827;0;267 or 06/04/2007;CASH;0;129

This is the view that i have created :

CREATE OR REPLACE VIEW "VW_CA_GL_AGENT_FEES" AS
SELECT
VW.NRIC_NEW,
VW.AGENT_STATUS,
VW.INP_DT_TM,
to_date(SUBSTR (VW.fee_info,0,INSTR (a.fee_info, ';') - 1),'dd/mm/yyyy') as BANK_IN_DT,
VW.CHQ_NO,
VW.AMOUNT,
VW.TOTAL_AMOUNT
FROM
t_exam_settings vw
where vw.rectype = 'L' and
vw.agent_status = 'A';

When i try to execute this view :

select * from VW_CA_GL_AGENT_FEES where bank_in_dt >= '01-Apr-2008'

I have encounter this error :

ORA-01830 : date format picture ends before converting enrire input string

May i know what is the problem at here as i can't figure out?

Thanks & Regards,
Iris

Report message to a moderator

Re: Convert string to date format [message #319745 is a reply to message #319744 ]

Mon, 12 May 2008 22:22

anacedent
Messages: 5842
Registered: July 2005
Location: surf meets turf in SoCal

Senior Member

>May i know what is the problem at here as i can't figure out?

ORA-01830 : date format picture ends before converting enrire input string

Please realize that with Oracle characters between single quote marks are STRINGS!

'This is a string, 2008-05-08, & not a date datatype'

When you want/need a date datatype, use TO_DATE() function!

Report message to a moderator

Re: Convert string to date format [message #319746 is a reply to message #319745 ]

Mon, 12 May 2008 22:39

iriswancy
Messages: 15
Registered: December 2005
Location: Malaysia

Junior Member

Hi,

Thanks for reply!

The data in the t_exam_settings.fee_info is 10/04/2007;PBB310827;0;267. So i have to get the first 10 characters which is the date (10/04/2007) to display. Thats why i used this : SUBSTR (VW.fee_info,0,INSTR (VW.fee_info, ';') - 1). Then i have used to_date() to convert this SUBSTR (VW.fee_info,0,INSTR (VW.fee_info, ';') - 1) into date format.

However when i try to execute my view :

select * from VW_CA_GL_AGENT_FEES where bank_in_dt >= '01-Apr-2008'

I got the ORA-01830:...error. I just not understand that i have declare my value to date format and why i get the error when i execute with this condition "where bank_in_dt >= '01-Apr-2008'"?

Thanks & Regards,
Iris

Report message to a moderator

Re: Convert string to date format [message #319747 is a reply to message #319744 ]

Mon, 12 May 2008 22:48

anacedent
Messages: 5842
Registered: July 2005
Location: surf meets turf in SoCal

Senior Member

use CUT & PASTE to show results from

SELECT SYSDATE FROM DUAL;

SELECT 
to_date(SUBSTR (VW.fee_info,0,INSTR (a.fee_info, ';') - 1),'dd/mm/yyyy') as BANK_IN_DT 
FROM t_exam_settings vw
where vw.rectype = 'L' and
vw.agent_status = 'A' AND
ROWNUM < 2
/

[Updated on: Mon, 12 May 2008 22:49]

Report message to a moderator

Re: Convert string to date format [message #319748 is a reply to message #319747 ]

Mon, 12 May 2008 22:59

iriswancy
Messages: 15
Registered: December 2005
Location: Malaysia

Junior Member

Hi,

Here are the result :

SELECT SYSDATE FROM DUAL

13-May-2008 12:05:09 PM

SELECT
to_date(SUBSTR (VW.fee_info,0,INSTR (a.fee_info, ';') - 1),'dd/mm/yyyy') as BANK_IN_DT
FROM
t_exam_settings vw
where vw.rectype = 'L' and
vw.agent_status = 'A' AND
ROWNUM < 2

06-Apr-2007 12:00:00 AM

Thanks & Regards,
Iris

Report message to a moderator

Re: Convert string to date format [message #319751 is a reply to message #319744 ]

Mon, 12 May 2008 23:15

anacedent
Messages: 5842
Registered: July 2005
Location: surf meets turf in SoCal

Senior Member

iriswancy wrote on Mon, 12 May 2008 20:17

This is the setting in the Oracle database
NLS_DATE_FORMAT = dd-Mon-yyyy

How do you reconcile the statement above with previous posted results as shown below?

>SELECT SYSDATE FROM DUAL
>13-May-2008 12:05:09 PM

Report message to a moderator

Re: Convert string to date format [message #319755 is a reply to message #319751 ]

Mon, 12 May 2008 23:30

iriswancy
Messages: 15
Registered: December 2005
Location: Malaysia

Junior Member

Hi,

When i execute this statement : select * from nls_session_parameters
It return me : NLS_DATE_FORMAT = dd-Mon-yyyy

Is it means that the NLS_DATE_FORMAT should be set to this value = dd-Mon-yyyy HH.MI.SSXFF AM?

Pls correct me if i am wrong because i am still a little bit not understand as i thought SYSDATE is from
NLS_DATE_FORMAT & NLS_TIME_FORMAT.

Thanks & Regards,
Iris

Report message to a moderator

Re: Convert string to date format [message #319776 is a reply to message #319755 ]

Tue, 13 May 2008 00:45

S.Rajaram
Messages: 644
Registered: October 2006
Location: United Kingdom

Senior Member

Quote:

SELECT
VW.NRIC_NEW,
VW.AGENT_STATUS,
VW.INP_DT_TM,
to_date(SUBSTR (VW.fee_info,0,INSTR (a.fee_info, ';') - 1),'dd/mm/yyyy') as BANK_IN_DT,
VW.CHQ_NO,
VW.AMOUNT,
VW.TOTAL_AMOUNT
FROM
t_exam_settings vw
where vw.rectype = 'L' and
vw.agent_status = 'A';

I am not sure how this is working because you are using an incorrect alias to select fee_info.

SQL> desc test_tab;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SNO                                       NOT NULL NUMBER(4)

SQL> select a.sno from test_tab b;
select a.sno from test_tab b
       *
ERROR at line 1:
ORA-00904: "A"."SNO": invalid identifier

Execute the following query and you will find out why it is failing with the mentioned error.

select fee_info from t_exam_settings where
INSTR (fee_info, ';') > 11

Regards

Raj

[Updated on: Tue, 13 May 2008 00:45]

Report message to a moderator

Re: Convert string to date format [message #319777 is a reply to message #319755 ]

Tue, 13 May 2008 00:47

Michel Cadot
Messages: 16994
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member

Quote:

Pls correct me if i am wrong because i am still a little bit not understand as i thought SYSDATE is from
NLS_DATE_FORMAT & NLS_TIME_FORMAT.

You are wrong.
SYSDATE default format is NLS_DATE_FORMAT alone.

Regards
Michel

Report message to a moderator

Re: Convert string to date format [message #319786 is a reply to message #319777 ]

Tue, 13 May 2008 01:00

iriswancy
Messages: 15
Registered: December 2005
Location: Malaysia

Junior Member

Hi,

Yes i got my results already....thanx a lot...its due to this INSTR (a.fee_info, ';')...

Many thanks guys!!

Thanks & Regards,
Iris

Report message to a moderator

Previous Topic:	Do we need to raise exception while using ref cursor?
Next Topic:	How to create email when you update the table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 4 16:06:01 CDT 2008

Total time taken to generate the page: 0.02198 seconds

.:: Forum Home :: Site Home :: Wiki Home :: Contact :: Privacy ::.