SQL & PL/SQL :: Date To Char / Char To Date

Dec 13, 2012

oracle 11 g xe

1) I want to pass date in '12 Dec 2012'(CHARECTER) while calling SP and in side SP i have declared parameter as DATE so I think it should not create any problem in any condition whether it is passed from oracle client or any other front end?

2) I have date in (date type variable) , how to convert it into char but it should be in iso format (i want iso format , so that it should not create any problem in any condition).

3) How to convert (date in char- in iso or any good format) into (date type variable) (so that it should not create problem and this is why i wanted char date in iso format).

4) If there is any good format and function to convert (date to char) and (char to date)?

View 5 Replies


ADVERTISEMENT

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

SQL & PL/SQL :: Char To Date Conversion?

Feb 7, 2013

converting a string to date, my string looks like '01022013' '04022013', I tried to_date('01022013', 'dd-mm-yyyy') but couldn't work.

View 6 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

SQL & PL/SQL :: Search Swedish / Danish / German Char Using English Char

Oct 27, 2010

I want to pass english character as a parameter and search a string that having swedish character, this needs to be done for all the swedish characters.

test _t table having the below 3 values

päiväp
metervara
flerfärgad

1. If user searches based on english char like below then they should get all the 3 values bcz fist & last have swedish ä and second one having english a.

Query : select name from test_t where name like '%a%'

Regsult:
päiväp
metervara
fräg

2. If user searches based on swedish char like below then they should get only 2 values bcz fist & last have swedish ä and second one having english a.

select name from test_t where name like '%ä%'

päiväp
fräg

Is any in-built function available, for Text alternative string search. If not how to search the string based on the query.

View 6 Replies View Related

Selection Criteria Char Between Char

Jul 22, 2009

I have a table with column val1 having data's starting with 0920 and 4 digit char values.

so we have the query to find

select * from table
where val1 between '0920' and 'ZZZZ'

will it work fine?

View 1 Replies View Related

SQL & PL/SQL :: Display Date Ranges In One Column As Separate Date Periods (start And End Date) In Two?

Jun 1, 2010

I'm trying to work out how to take a table like this:

IDDate
12502-Feb-07
12516-Mar-07
12523-May-07
12524-May-07
12525-May-07
33302-Jan-09
33303-Jan-09
33304-Jan-09
33317-Mar-09

And display the data like this:

IDPeriodPeriod StartPeriod End
125102-Feb-0702-Feb-07
125216-Mar-0716-Mar-07
125323-May-0725-May-07
333102-Jan-0904-Jan-09
333217-Mar-0917-Mar-09

As you can see, it's split the entries into date ranges. If there is a 'lone' date, the 'period start' and the 'period end' are the same date.

View 13 Replies View Related

Forms :: Expiry Date To Automatically Show A Date 15 Years After Initial Date

Apr 12, 2010

I have a two date fields in my form; valid from date and expiry date.

Currently my valid from date has an inital value property of $$date$$ which automaitcally brings up todays date.

I need my expiry date to automatically show a date 15 years after this date?

View 8 Replies View Related

Oracle 9i To 10g To Char Function?

Feb 11, 2009

Well the company i work for has just recently upgraded from Oracle 9i to 10g. We are having various problems with the migration, and with certain code breaking. My question regards this piece of code;

[code]

to_number(to_char('a_date','YYYY'))

[code]

There are various statements like the one above scattered throughout a query i am trying to fix. When run, the query returns an "invalid number ora-01722" error, which i know is caused by the above code.

if this method of converting from date, to character was discontinued from 9i to 10g?it just seems strange as there are a lot of statements like the one above and they must have worked at some point, but now i cant even get one to work on its own.

View 3 Replies View Related

SQL & PL/SQL :: Convert Char To Number

Mar 16, 2011

I've got a table with varchar records and I need to separate numerical value and convert into the number datatype.

create table tmp_mape

(remark varchar2(100) )

insert into tmp_mape values ('Dobitie zdarma 3,32EUR 0910105067 02/02')
insert into tmp_mape values ( 'Dobitie pravidelné 9,00EUR' )

I just need to get values 3.32 and 9 from that example into the new column.

The select below returns ORA-01722: invalid number

how to solve it?

select remark,
to_number( to_number(REGEXP_SUBSTR (remark, '[[:digit:]]+', 1) ) ||','|| to_number(substr(REGEXP_SUBSTR (remark, ',[^EUR]+', 1) ,2) ) )
from tmp_mape

View 7 Replies View Related

SQL Formatting To Char Function

Dec 23, 2012

i have a problem with my SQL programming which i am learning at the moment. Currently here is my Show how many orders were placed in each month of 2012. (group by question, to char function)

select count(orderdate)
from csorder
group by orderdate;

where the to char function would occur?

tables for reference
CSPRODUCT contains (PRODCODE, PRODNAME, CATCODE, PRICE, PARTOF_PRODCODE)
CSCATEGORY contains (CATECODE, CATNAME)
CSORDER contains (ORDERID, ORDERDATE, CUSTID, PAID)
CSORDERLINE contains (ORDERID, PRODCODE, QUANTITY)

View 3 Replies View Related

SQL & PL/SQL :: Return A List Of Non Standard Char

Oct 9, 2013

I have been tasked with producing a list of all non standard characters that are not one of 0-9, a-z, A-Z from a parts table. The list should capture any non-English characters i.e. áóú etc as well as !"£$%^&* etc. These are a small example of the data.

Part_Description
Probengefäss kompl.
Stützring zu Probenehmer
Zargendichtung m.2 Ventillöcher
Pneumatikdichtung m.2 Ventillöcher

Result from above example
ä.üö

View 8 Replies View Related

SQL & PL/SQL :: Comparing 2 Char Columns Using Trim?

Aug 18, 2011

I am trying to compare 2 char columns using trim to avoid space padding

CREATE TABLE TRIAL_A
(ABC CHAR(6));
INSERT ALL

[Code]....

View 11 Replies View Related

SQL & PL/SQL :: Passing The Char Variable In Cursor

Apr 9, 2013

i trying to pass the char varible to the cursor but it is not taking ,,, if i hardcode the values to the cursor it is taking

here is the detailed program ... why this is not taking and tell me how to pass the values through it..

declare
v_name char(6) ;
cursor c1(c_name char) is
select name, parent,child,status from relation
start with name='%'
connect by prior parent=child
union

[Code]...

View 9 Replies View Related

Forms :: Char Display Right To Left

Apr 1, 2011

I am using forms 6i and oracle 10g. i want to display a char from database. that means a database string 'Bangladesh' is display from by one char to one char.

and display view right to left first display "B" then "BA" then 'BAN' etc.

View 2 Replies View Related

SQL & PL/SQL :: How To Find Out Lowercase Char In A String

May 16, 2008

How to find out lowercase char in a String in a SQL query

for example

"ORAclE" here c and l are lowercase so how can i find out this condition... is there any build in function in oracle?

View 13 Replies View Related

SQL & PL/SQL :: USING Listagg Function For More Than 8000 Char

Oct 28, 2011

using listagg function for more than 8000 char.I did the below sample SQL and in "e_orig" and "d_orig" for upto 4000 char it is working fine but I have to use it for more than 8000 char. and it is giving error,I checked the listagg function is having limitation of 4000 char.I tried but I am unable to achieve this.

select d.dname,d.loc,e.hiredate
,listagg(e.ename,',' ) within group (order by e.deptno) over (partition by e.deptno) as e_orig
,listagg(e.ename, ',') within group (order by e.sal) over (partition by e.deptno) as d_orig
from emp e, dept d
where e.deptno=d.deptno;
[code]....

View 17 Replies View Related

Oracle 11g Different Decimal Char While Using Sproc

Jul 6, 2011

I am using an portuguese(brazil) Oracle 11g db,which should have ',' as decimal char. it seems to be working fine when i m trying to get numerical data via select query,but when i am trying to retrive the same data using out parameter of an sproc.I am getting data with '.' as decimal char.

View 2 Replies View Related

PL/SQL :: Changing Format From Numeric To Char

Jul 26, 2012

I want to get the last month of my table “a” (Which is in number format) and after that I want to change it to a date format and insert into my table “b”.

I have already done the max month query:

SELECT MAX(MONTH_ID) FROM Table_a;

SELECT DISTINCT MONTH_ID ---This data is the one that I would like to change format "mon-yyyy"
FROM Table_a
WHERE MONTH_ID = (SELECT MAX(MONTH_ID) FROM Table_a);

I am getting trouble with the to_char function, I have been reviewing how it works and how to handle it.

View 3 Replies View Related

PL/SQL :: Verify The Number And Char Column

Oct 12, 2012

From a list of values from a number column, I need to verify is there any values not satisfying number(3).

Ex- I have a column col1 where we have following values

'qwq'
'12.3'
'1'
'15'

Can I write a query to check the value not satisfying number(3) datatype.Eg the query should return

'qwq'
'12.3'

as the above two will not fit in to number(3) datatype columns

View 13 Replies View Related

SQL & PL/SQL :: Passing Bind Char Correctly

Dec 7, 2010

I tried google and most of bind example using number instead of character, ?

I have the code as below:

variable oldId varchar2(5)
exec :oldId :='&1'
variable newId varchar2(5)
exec :newId :='&2'

[Code]...

Since in TAB1, ID is defined as varchar2(5), and I have defined both oldId and newId as varchar2(5) in my code, I wonder why it doesn't work.

It returned this error:

DECLARE
*
ERROR at line 1: ORA-01722: invalid number ORA-06512: at line xx

After supply the value for oldId(val2) and newId(val1)

SQL > @thisScript val1 val2

I use dbms_output.put_line to print the command executed, it seems it is doing

UPDATE TABLE1 SET ID = val1 WHERE ID = val2

without the single quote ('val1'), ('val2'). But if I put in single quotes, the output statement

UPDATE TABLE1 SET ID = ':newId' WHERE ID = ':oldId'

The same bind logic works in SQL prompt

SQL > variable oldId varchar2(5);
SQL > variable newId varchar2(5);
SQL > exec :oldId :='val2'
SQL > exec :newId :='val1'
SQL > UPDATE TABLE1 SET ID = :newId WHERE ID = :oldId;
1 row updated.

I am wondering what should I do then? With or without single quotes?

View 9 Replies View Related

Performance Of CHAR Versus VARCHAR2 In VLDB DW

Jul 20, 2010

With a very large database (VLDB) for a data warehouse (DW) using primarily a STAR based schema in an environment in which time (both human and CPU) is orders of magnitude more valuable than storage capacity, is there any signficant difference in query performance when tables have all fixed length (CHAR) columns compared to tables with variable length (VARCHAR2) columns?

I realize this is one of those "in general" questions so considering "a given VLDB DW environment" with all other things being equal, what, if any, is the time based performance difference between a database of tables with all fixed sized columns versus one of tables with variable length columns ?

View 2 Replies View Related

Forms :: Format Mask To Char Field

Jun 16, 2010

I want to add format mask on my field...

my field datatype is char in forms and in database VARCHAR2(30) and I want to add format mask as follows

12345-1234567-1

in format mask of this field property I give it to format mast like as

FMAAAAA"-"AAAAAAA"-"A

when I run the form and when I write and exit from this field then it returns the error.

FRM-40209: Field must be of form FMAAAAA"-"AAAAAAA"-"A

EDIT by VK: Seems your CAPS lock is on or the Shift is stuck.

View 5 Replies View Related

SQL & PL/SQL :: Existing Database Conversion From Bytes To Char?

Aug 11, 2011

What is the best way to convert an existing database table set with nls_legnth_semantic=BYTE to CHAR?

View 2 Replies View Related

SQL & PL/SQL :: Finding Hours And Minutes Between Two Char Field?

Mar 24, 2012

I want to find the hours and minutes between two char field data type.Example I have two char columns one is "start_time" and another one is "end_time".The start time and end time is the machine reading of CNC MACHINE in manufacturing.I want to develope the package to capture the actual machine running time.But I have the start and end time reading in character field.The machine reading format is hour:minutes:seconds only.It will looks like 1234:45:23(the machine ran 1234 hours and 45 minutes and 23 seconds).See the below table to understand my requirements.

start_time end_time result in hours & minutes
---------- -------- -------------------------
345 347 2 hrs
347 350 3 hrs
350 357.20 7 hrs and 20 minutes

If I subtract end_time - start_time I will get the result in char type only not in hours and minutes format.Another example

start_time end_time result in hours & minutes
---------- -------- -------------------------
357.21 360.40 3.19(If subtract end_time - start_time)

View 2 Replies View Related

PL/SQL :: ORA-06525 - Length Mismatch For CHAR Or RAW Data

Nov 1, 2013

ORA-06525: Length Mismatch for CHAR or RAW dataORA-06512: at "SYS.UTL_FILE", line 127ORA-06512: at "SYS.UTL_FILE", line 1204ORA-06512: at line 14

View 20 Replies View Related

PL/SQL :: Retrieve Item With None Of Its Char Values As Null?

Oct 12, 2012

I have a table something as below

Things_t
Things Characteristic Char Value
Item 1 Colour Red
Item 1 Packaging
Item 2 Shape Square
Item 2 Brand Spunk

Now i want to reterive an item with none of its char values as Null. Using the query “ select distinct things from things_t where char value is Null ” will fetch the item 1 also together with item 2. i want to fetch a record from thing for which none of the char values are Null such as Item 2.

View 9 Replies View Related

PL/SQL :: Passing Char Variable In Cursors - Not Taking?

Apr 9, 2013

i trying to pass the char varible to the cursor but it is not taking ,,, if i hardcode the values to the cursor it is takinghere is the detailed program ... any body can tell why this is not taking and tell me how to pass the values through it..

declare
v_name char(6) ;
cursor c1(c_name char) is
select name, parent,child,status from relation
start with name='%'

[code]....

View 4 Replies View Related

Forms :: Error In Concat A Clob With Char Datatype

Nov 2, 2011

How could we concat a clob with a char datatype.

declare
a clob;
b varchar2;
c clob;
begin
c:=a||b;
end;

When execute above code in form runtime, I get error ora-32767.

"ORA-29287: invalid maximum line size Cause: An invalid maximum line size value was specified.
Action: Correct the maximum line size to be in the range [1, 32767]."

View 3 Replies View Related

SQL & PL/SQL :: ORA-00932 Inconsistent Datatypes - Expected NUMBER Got CHAR

Aug 17, 2010

My table looks like this ->
Dp_value
124325
2434
3536
3536

Code is ->
(case when CL.DECIMALPLACES = 0 and CL.FIELDTYPE = 'D' and cl.DATATYPE = 'N'
then trim(dp."Value")*1000
else dp."Value" end) as dp_value,

What I am trying to do ->
From another table cl I want to say if decimal place is 0, fieldtype is decimal (D/C) and datatype is number (N/T)then multiply the number with 1000.

If not then leave it as it is.

When I run my code I get the error:
ORA-00932 : inconsistent datatypes: expected NUMBER got CHAR
for else dp."Value" end)

View 9 Replies View Related







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