Home » SQL & PL/SQL » SQL & PL/SQL » Convert string to date format
Convert string to date format [message #319744] Mon, 12 May 2008 22:17 Go to next message
iriswancy
Messages: 17
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
Re: Convert string to date format [message #319745 is a reply to message #319744] Mon, 12 May 2008 22:22 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: 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!
Re: Convert string to date format [message #319746 is a reply to message #319745] Mon, 12 May 2008 22:39 Go to previous messageGo to next message
iriswancy
Messages: 17
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
Re: Convert string to date format [message #319747 is a reply to message #319744] Mon, 12 May 2008 22:48 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: 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] by Moderator

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 Go to previous messageGo to next message
iriswancy
Messages: 17
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
Re: Convert string to date format [message #319751 is a reply to message #319744] Mon, 12 May 2008 23:15 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: 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
Re: Convert string to date format [message #319755 is a reply to message #319751] Mon, 12 May 2008 23:30 Go to previous messageGo to next message
iriswancy
Messages: 17
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
Re: Convert string to date format [message #319776 is a reply to message #319755] Tue, 13 May 2008 00:45 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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
Re: Convert string to date format [message #319786 is a reply to message #319777] Tue, 13 May 2008 01:00 Go to previous message
iriswancy
Messages: 17
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
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:
  


Current Time: Tue Dec 06 04:32:00 CST 2016

Total time taken to generate the page: 0.07604 seconds