SQL & PL/SQL :: JSP Limitations - Date Format Picture Ends Before Converting Entire Input String

Feb 21, 2010

I am using

SELECT TO_CHAR(TO_DATE((:BILL_VALUE),'J'),'Jsp') FROM sys.dual;

its givinig right result for

1023411->One Million Twenty-Three Thousand Four Hundred Eleven

but if i will change it to 10234111 then this query giving an error

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

Is there any limitation or is there any other built in function to get the number to a word.

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Date Format Picture Ends Before Converting Entire Input String

Jun 29, 2011

I am trying to insert a row in a table and getting the below error.

SQL> insert into tbl_force_charging(ANI, date_time, durations,src, circleid)
2 values ('9569333585','29-JUN-11 03.19.41.000000000 PM','1027','51010','BIR'
)
3 ;
values ('9569333585','29-JUN-11 03.19.41.000000000 PM','1027','51010','BIR')
*
ERROR at line 2: ORA-01830: date format picture ends before converting entire input string

Table Structure is

Name Null? Type
----------------------------------------- -------- ---------------------------
ANI VARCHAR2(10)
DATE_TIME DATE
DURATIONS VARCHAR2(10)
SRC VARCHAR2(10)
CIRCLEID VARCHAR2(10)
SQL>

View 11 Replies View Related

SQL & PL/SQL :: ORA-01830 / Date Format Picture Ends Before Converting Entire Input String

Feb 26, 2013

when i run this query i am facing date format error.

select sbrueregister.UEIMSI,sbrueregister.fapid,sbrfapslid.slid,sbrfapslid.ACTIVATION_TS,sbrfapslid.DEACTIVATION_TS from SBRFAPSLID INNER JOIN sbrueregister ON sbrfapslid.fapid=sbrueregister.fapid where sbrfapslid.slid='1234567890' and sbrueregister.registeredat between TO_DATE('2013-02-1.12.0. 10. 123000000','YYYY-MM-DD HH.MI.SS.SSSSSS')and TO_DATE('2013-02-1.12.9.10.123000000', 'YYYY-MM-DD HH.MI.SS.SSSSSS');

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

View 1 Replies View Related

SQL & PL/SQL :: Converting Comma And Pipe Delimited Input String To Nested Table

Nov 12, 2010

I am expecting the input to my procedure will be in the following format

'AAA, aaa, Aa12|BBB, bbb, bb2B|dd3, DDDE,ddd67'

I need to convert it to nested table and when I query the nested table , the output should be

column_value
------------
AAA
aaa
Aa1
BBB
bbb
bb2B
dd3
DDDE
ddd67

View 9 Replies View Related

Client Tools :: DATE FORMAT Without Converting To Char?

Apr 4, 2012

select to_date('31-12-2012','DD-MM-RRRR') from dual;

OUT PUT : '12/31/2012'

is't possible to show the date format like

'31-12-2012'

without converting to char

View 8 Replies View Related

Export/Import/SQL Loader :: Converting Date Format Using RTRIM

Apr 22, 2013

I need loading date formats using sqlldr. I am trying to load some data from another source into our Oracle database, however, the "date" field includes extra characters that i'm trying to deal with. For reference, I'm running Oracle 11.2 on Linux 5.7.

First, the actual date input is:

April 18, 2013 8:50:44 AM EDTI tried to input it a few ways but was unable to load it until I physically removed the "AM EDT" from the input string.

My first question is if there is a way to RTRIM and get rid of those extra characters. Either that, or I need a way to convert the "AM EDT" into our standard date format.

My control file has the following:

LASTCHANGEDAT date "Month dd, YYYY HH MI SS",This will load the data, but only if I strip out the "AM EDT" from the actual data input which is not feasible.

Also, how would I handle date that comes in as: April 18, 2013 3:50:44 PM EDT

Also, I tried the following, but it gives me a different error:

LASTCHANGEDAT "to_date(:LASTCHANGEDAT,'DD-MON-YYYY HH24:MI:SS')",

gives me error:

Record 1: Rejected - Error on table NTWKREP.INTERFACE, column LASTCHANGEDAT.
ORA-01858: a non-numeric character was found where a numeric was expected

View 5 Replies View Related

PL/SQL :: Input Parameter - Date Format

Aug 5, 2013

i have below requirement in one program date is input parameter. but that should work whatever date format like DD/mon/yyyyyyyy.mm.dd what shd i do input parameter from dateand to date

View 5 Replies View Related

SQL & PL/SQL :: How To Convert String To Date Format

Jun 12, 2011

I have a string in this format '2011/06/01 00:00:00'. How do I convert this to date format.I tried to_date,to_char and they give errors invalid number & literal does not math format string. I don't have much control on the string since that is the way is comes from the application.

View 3 Replies View Related

SQL & PL/SQL :: Store The Date In String Type Format

Nov 23, 2010

We store the date in String Type format of "MON-YYYY", we require to run the report base on that string contain date column, During the Query execute the Error Display of (ora-01858: a non-numeric character was found where a numeric was expected)

The Query is :

select period_name,doc_sequence_value,doc_sequence_id,date_created,name,JE_SOURCE,XX
from
(
select period_name,doc_sequence_value,doc_sequence_id,date_created,name,JE_SOURCE,TO_DATE(XX,'DD/MM/RRRR') XX
from
(
select TO_CHAR(TO_DATE(('01-'||substr(gl.period_name,1,3)||'-'||substr(gl.period_name,5,2)),'DD/MM/RR'),'DD-MON-RRRR') xx,

[code].....

View 4 Replies View Related

UPDATE Date / Literal Doesn't Match Format String

Nov 29, 2007

When I try to update a date column with this:

 UPDATE
        event_request
    SET
        REQ_EVENT_DATE = TO_CHAR(REQ_EVENT_DATE - INTERVAL '1' HOUR, 'yyyy-mm-dd hh24:mi')
    WHERE
        eventID=123 

Oracle returns this error:

 ERROR at line 4:ORA-01861: literal does not match format string 

where line 4 is REQ_EVENT_DATE = TO_CHAR(REQ_EVENT_DATE - INTERVAL '1' HOUR, 'yyyy-mm-dd hh24:mi').The REQ_EVENT_DATE field was originally populated with this stripped down query:

$date = '2007-12-25 08:35'; INSERT INTO (REQ_EVENT_DATE) VALUES (to_date('$date', 'yyyy-mm-dd hh24:mi'));

So - what is wrong with the UPDATE query?

View 1 Replies View Related

PL/SQL :: Entire Date / Time Value In A Date Field

May 21, 2013

I am having problems with the XMLTable function. I cant get it to see the entire date/time value in a date field. This wont work

select x1.* from XMLTABLE('/DOCUMENT' passing xmltype('<DOCUMENT><STR>abc def ghi</STR><NUM>1234</NUM><DT>2013-02-17T04:24:02</DT></DOCUMENT>') columns STR varchar2(25), NUM number, DT date) x1;

However if I change the DT tag to just the date only "2013-02-17" it works. Why wont Oracle see the entire date/time format even if its ISO 8601 compliant?

Oracle DB: 11.2.0.3.0

View 3 Replies View Related

Converting Date Field To Datestamp (Epoch Date)

Apr 11, 2013

I have a small prolem thats best described like this....

a table called TONY with a field named VISITED (date as YYYYMMDD).

We want to populate the field TIMESTAMP (Last visited timestamp, 18 digits) using midnight or 00:00:00 on VISITED value.

Something like:

UPDATE TONY SET TIMESTAMP = �(whatever the formula is involving VISITED).

but i cannot figure out the best way to derive the TIMESTAMP value...

it's a date to epoch conversion, and i can find many examples of Epoch to date, but thats the wrong way around for me i'm afraid!

Oracle 11gR2 by the way...

View 4 Replies View Related

SQL & PL/SQL :: Converting Char Into Date With Column Having Non-date Formats

Aug 16, 2010

i have a table with the following description

create table gl_periods(period_name varchar2(10),transactions number (2) );

with the data as :

period_name transactions
------------ --------------
JAN-10 12
FEB-10 12
MAR-10 8
APR-10 23
ADJ_TOM-10 25
MAY-10 37
JUN-10 41
JUL-10 10
PHY_JAY-10 6
AUG-10 14
SEP-10 22

My requirment is to find out the period names and transactions which are in valid date formats and are less than sysdate and the non date formats are adjustments made by different users for their transactions

View 8 Replies View Related

Forms :: Converting Rdf Report To Excel Format

Jun 18, 2010

I have installed Rep2excel software on pc but i m not able to convert oraclr report to excel format.

View 1 Replies View Related

SQL & PL/SQL :: Converting Number To String?

Apr 27, 2012

I found this query in my sub version repository and really wondering how this working

select to_char(to_date(1000000,'J') ,'JSP') string_value from dual;

View 1 Replies View Related

PL/SQL :: Retrieve Date Range From 2 Search Input Date Fields

Aug 16, 2012

I would like to inquire how do I code my select statement if my user enter a search date range(search startDt: 01/08/2012 and search endDt :30/09/2012) and I will like to retrieve only the 7records out from my table as shown below ?

STARTDT_     ENDDT_
01/08/2012 01/08/2012
01/06/2012 31/12/2012
01/08/2012 01/08/2012
01/09/2012 01/09/2012
01/09/2012 31/12/2012
01/07/2012 01/07/2012->not retrieved out
01/08/2012 01/08/2012
01/01/2012 31/12/2012

View 6 Replies View Related

Reports & Discoverer :: Converting The Number To String

Dec 6, 2010

I wanna convert the amount of money from number to string such as 144.5 to be one hundred forty four point five is there any function or i have to write my function? How could i put new line in the string?

for example if i have 'SAB Bank' || 'Riyadh'

but i want SAB bank to be displayed in line and Riyadh in line.

View 2 Replies View Related

SQL & PL/SQL :: Output Data Format From Input Table?

May 18, 2011

how can i get the output format from given input table

View 2 Replies View Related

SQL & PL/SQL :: Remove Special Characters From Input String?

Aug 5, 2010

I have a following table,

create table test1(col1 varchar2(20));

insert into test1 values('4711-3/01');

I believe we need to use Translate function to get rid of special characters, But I would not be knowing what sort of special charecters which appear in the string, In that case how do I use Translate?

View 29 Replies View Related

SQL & PL/SQL :: Read Only The Input String From BLOB Datatype Column?

Mar 28, 2013

I have a table that has column with BLOB datatype. I am trying read only the inputted String from the BLOB datatype column. I have used the below query,

SELECT utl_raw.cast_to_varchar2(column1) FROM TAB1
and it gives the result as
<H2><FONT color=#cc0000><EM><U>test</U></EM></FONT></H2>

However I would like to extract only the string "test" which is inputted. Is there any build-in function in oracle which will satisfy this requirement?

View 7 Replies View Related

SQL & PL/SQL :: Multipurpose Varchar2 Column Error When Converting To Date

Aug 16, 2011

i have a varchar2 column containing string values that can be converted to date i.e. ('31-JUL-11') and that column also contains text strings in it. i.e. ('Some string data...')

records whose column value can be converted to date are extractable via where clause (i.e. those rows are associated with some fix number / flag)

now when i try to use to_date function i get the error that

" ORA-01858 a non-numeric character was found where a numeric was expected "

in sql i have added a where clause to only pick rows with flag, but even then it gives the error.

using a subquery in the from clause eliminates the error, but when i create it in a view it again gives the same error.

View 8 Replies View Related

Visual Studio :: Set Query Window Default Date Format To Full Date?

Oct 24, 2013

When I run a query form the the Query Window in Visuial Studios 2012 all the date fields truncated to 'mm/dd/yyyy', but i need the full date returned. I am able to get full date from  TO_char(MyDateField, 'yyyy-mm-dd hh24:mi:ss'), but if I do TO_DATE(MyDateField, 'yyyy-mm-dd hh24:mi:ss')  it only returns  'mm/dd/yyyy'. I'm sure this is a simple setting in Visual studios but I cant find it to save my life. Is there there a way to have the full date returned by default?

View 0 Replies View Related

Forms :: Date Field Gets Disabled When Date Format Is Given?

Oct 3, 2012

A field named xxx_date is a text item which we have to enter manually so as to update a record in that particular date. This is a mandatory field without which we cannot continue the data entry..

I am getting this error while trying to update the record

FRM-40509 :Oracle error :unable to update record

I have kept the enabled = yes
required=no
data type=Date.. in the property pallet

View 2 Replies View Related

PL/SQL :: Reset Date In Specific Format (Date / Time)

Dec 23, 2012

I want to reset my date to this format: 12/31/2012 11:59:59 PM - see code below:

DECLARE
v_latest_close DATE;
BEGIN
v_latest_close := TO_DATE ('12/31/2012 23:59:59 ','MM/DD/YYYY HH24:MI:SS');
DBMS_OUTPUT.PUT_LINE('The new date format is : '|| v_latest_close);
END;

the code above displays only : 12/31/2012 instead of 12/31/2012 11:59:59 PM

View 4 Replies View Related

SQL & PL/SQL :: How To Use Date As Input Parameter

May 13, 2013

How to use date as an input parameter,im supposed to use varchar2 as the data type

CREATE OR REPLACE PROCEDURE mail1 ( recievers VARCHAR2 ,p_date in varchar2 )
IS
sender VARCHAR2(30) := 'xyz@gmail.com';
mailhost VARCHAR2(100) := 'host address';
TAB VARCHAR(2) := CHR(9);
mail_conn utl_smtp.connection;

[code].....

cursor c1 is

--select activity_date,procedure_name,status_message,error_desc from staging_activity_log where rownum between 1 and 10 ;
select activity_date,procedure_name,status_message,error_desc from staging_activity_log where error_desc is not null and trunc(activity_date) >= to_date(p_date,'DD-MON-YYYY') ;
BEGIN

[code].....

when i execute i get

BEGIN mail1 ('xxx@gmail.com,yyy@gmail.com,'28-jan-2008'); END;
Error at line 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "MAIL1", line 27
ORA-06512: at line 1

View 3 Replies View Related

SQL & PL/SQL :: Converting HTML Format Text To Plain Text?

Dec 23, 2009

Is there any way to convert HTML format text to Plain Text ?

View 26 Replies View Related

SQL & PL/SQL :: Date Query - Input Year In Specified Range

Dec 26, 2012

Getting problem with below query.

select to_date('30-DEC-00','DD-MON-YYYY') from dual

Error starting at line 1 in command:

select to_date('30-DEC-00','DD-MON-YYYY') from dual

Error report:

SQL Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0"

*Cause: Illegal year entered
*Action: Input year in the specified range

View 1 Replies View Related

SQL & PL/SQL :: Convert As Procedure Based On Input Date Parameter

Dec 12, 2012

i have the below query

select to_char(report_date, 'YYYY MM Mon'), count(1) no_of_times
from (
select to_date('&&YYYYMMDD', 'YYYYMMDD')+rownum report_date
, mod(rownum,14) mod_result
from all_objects

[code]...

need to convert as procedure based on input date parameter.I will pass the input date from java environment and need to see the sql query output in front end.

View 7 Replies View Related

SQL & PL/SQL :: Calculate Balance For Last Two Cooperative Years On Input Date

Aug 24, 2012

I need to calcuate balance for last two cooprative years on input date.

example

if input date is-->"01-AUG-2012" if (month >=7) -->then i need to calculate from 01-july-2010 to 30-june-2011 and 01-july-2011 to 30-june-2012 and 01-july-2012 to input date.

if input date is-->"01-june-2012" if (month<7) -->then i need to calculate from 01-july-2009 to 30-june-2010 and 01-july-2010 to 30-june-2011 and 01-july-2011 to input date.

how to achieve this logic

View 4 Replies View Related

SQL & PL/SQL :: Split A String As Per Required Format

Dec 23, 2010

Split a column with values like 1-2-21-3 into 001-002-21-003 (ie format required is 000-000-00-000) using sql.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved