PL/SQL :: Check If Data Stored In Varchar2 Type Column Is Date Or Not

Jun 29, 2012

Is there a seeded function by which I can check all the rows which stored dates in varchar column.

I have a table say test (test_data varchar2(100));

Now I will insert all types of records into the table varchar,number dates and then i will write q query to etch all those records only which has dates only

INSERT INTO test(1);
INSERT INTO test('ABC');
INSERT INTO test(SYSDATE);
INSERT INTO test(TO_CHAR(SYSDATE,'DD-MON-YYYY'));
INSERT INTO test(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
INSERT INTO test('15/01/2012');
...
commit;

View 17 Replies


ADVERTISEMENT

SQL & PL/SQL :: Convert VARCHAR2 To Date Data Type

Jun 13, 2012

I have problem to convert simply varchar to date data type.

For example: 2012-05-28 22:36:08 and I would like to get format 28.5.2012 22:36:08

However I try to do it I get always some errors.

select '2012-05-28 22:36:08',
to_date( '2012-05-28 22:36:08', 'dd.mm.yyyy HH24:MI:SS') ,
to_char('2012-05-28 22:36:08', 'dd.mm.yyyy HH24:MI:SS')
from dual

View 7 Replies View Related

SQL & PL/SQL :: Column Of Table With Data Type Varchar2 - Corresponding Index

Jan 10, 2013

I have an index on column of table which of data type varchar2. While selecting data from that table I am using following scenarios in where on the indexed column

like '%abc%'
like 'abc%'
like '&abc'

Will be the corresponding index will be for those cases?

View 3 Replies View Related

PL/SQL :: How Rownum Works For The Column Having Data Type As Varchar2

Jan 29, 2013

bookshelf_test table structure

title     varchar2(100)     y          
publisher     varchar2(20)     y          
categoryname     varchar2(20)     y          
rating     varchar2(2)     y

my query is,
select ROWNUM AS "Rank",title,publisher from (select rating,title,publisher from bookshelf_test order by rating desc ) where ROWNUM <=3

returns result ,

1     1     MY LEDGER     KOCH PRESS
2     2     TO KILL A MOCKINGBIRD     HARPERCOLLINS
3     3     THE MISMEASURE OF MAN     W.W. NORTON        

But inner query (select rating,title,publisher from bookshelf_test order by rating desc ) returns,

1     5     WONDERFUL LIFE     W.W.NORTON
2     5     THE MISMEASURE OF MAN     W.W. NORTON
3     5     TO KILL A MOCKINGBIRD     HARPERCOLLINS
4     5     MY LEDGER     KOCH PRESS
5     4     TRUMAN     SIMON SCHUSTER
6     4     GOSPEL     PICADOR
7     4     HARRY POTTER AND THE GOBLET OF FIRE     SCHOLASTIC
8     4     INNUMERACY     VINTAGE BOOKS
9     4     JOHN ADAMS     SIMON SCHUSTER
10     4     JOURNALS OF LEWIS AND CLARK     MARINER
11     4     LETTERS AND PAPERS FROM PRISON     SCRIBNER
12     4     PREACHING TO HEAD AND HEART     ABINGDON PRESS
13     4     THE SHIPPING NEWS     SIMON SCHUSTER
14     4     THE GOOD BOOK     BARD
15     4     THE DISCOVERERS     RANDOM HOUSE
16     3     THE COST OF DISCIPLESHIP     TOUCHSTONE
17     3     SHOELESS JOE     MARINER
18     3     KIERKEGAARD ANTHOLOGY     PRINCETON UNIV PR
19     3     EMMA WHO SAVED MY LIFE     ST MARTIN'S PRESS
20     3     EITHER/OR     PENGUIN
21     3     CHARLOTTE'S WEB     HARPERTROPHY
22     3     BOX SOCIALS     MARINER
23     3     ANNE OF GREEN GABLES     GRAMMERCY
24     3     WEST WITH THE NIGHT     NORTH POINT PRESS
25     3     UNDER THE EYE OF THE CLOCK     ARCADE PUB
26     3     TRUMPET OF THE SWAN     HARPERCOLLINS
27     2     COMPLETE POEMS OF JOHN KEATS     VIKING
28     1     POLAR EXPRESS     HOUGHTON MIFFLIN
29     1     GOOD DOG, CARL     LITTLE SIMON
30     1     MIDNIGHT MAGIC     SCHOLASTIC
31     1     RUNAWAY BUNNY     HARPERFESTIVAL

why final queries top 3 rows r different than inner query ?

View 3 Replies View Related

How To Order Date Which Has Its Type As Varchar2

Jan 26, 2011

I have table called INFO and the column called CREATED_DATE . Now the datatype of CREATED_DATE is of varchar2 . Now If I need to query the table through select statement where I need to order the result based on CREATED_DATE , how can i achieve this ?

View 1 Replies View Related

Server Administration :: Functions To Convert The Long Type Field Data To Varchar2 Type

Oct 17, 2011

Is there some functions to convert the long type field data to varchar2 type?

View 2 Replies View Related

SQL & PL/SQL :: Check Constraint To Disallow Number In Varchar2 Column

Apr 21, 2010

I have table customer which contains a column CUSTOMER_FIRST_NAME

CUSTOMER_FIRST_NAME VARCHAR2(50)

What will be sql statement to add a constraint on the CUSTOMER_FIRST_NAME column of the CUSTOMERS table so that the value inserted in the column does not have numbers ?

View 34 Replies View Related

SQL & PL/SQL :: Table With Modifieddate Column With DATE Data Type

Jun 4, 2012

I have a table with modifieddate column with 'DATE' data type.I am facing date format exception and tried with to_char, to_date but its throwing invalid number exception. how to format date accordingly.

SELECT * FROM EMP WHERE modifieddate > '31-Dec-2011 18:30';

ORA-01722: invalid number
01722. 00000 - "invalid number"

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

SQL & PL/SQL :: Date Check On A Number Column

May 16, 2011

I have a column defined as Number( 8 ) which is supposed to have date values. I would like to check if all the rows in that table have valid dates. We could use to_date(coulmn_name, 'YYYYMMDD') and catch the rownums for error conditions using pl/sql. I would like to know if we could just do it using sql only and return the row numbers for those that are invalid dates?

View 5 Replies View Related

Getting Time From Date Type Column?

Mar 3, 2011

I am working with Oracle 8i and I have a problem while applying the below query:

select to_char(ENTRYDATE,'MM-DD-YYYY HH24:MI:SS') from TBL_BANDWIDTH_READS
where ENTRYDATE > sysdate-1
and rownum <10
03-03-2011 00:00:00
03-03-2011 00:00:00
03-03-2011 00:00:00
03-03-2011 00:00:00

[code].....

The time appears as 00:00:00. I said it's something weird because if I take the where condition off, then something like this appears:

select to_char(ENTRYDATE,'MM-DD-YYYY HH24:MI:SS') from TBL_BANDWIDTH_READS
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27

I am using the same query on a different table and there's no problem on it:

select to_char(ENTRYDATE,'MM-DD-YYYY HH24:MI:SS') from TBL_XX_PROGRAM
where ENTRYDATE > sysdate -10 and rownum <10
03-03-2011 17:06:48
03-03-2011 17:06:48
03-03-2011 16:59:08
03-03-2011 16:59:08
03-03-2011 12:04:21

I checked the DDL of both tables and the only difference is that the TBL_XX_PROGRAM have the ENTRYDATE field defined as not null and the ENTRYDATE on the TBL_BANDWIDTH_READS does not have the same constraint, could it affect?

View 3 Replies View Related

XML DB :: XMLTable With Date Type Column

Sep 5, 2012

I am trying to run select query on xml using xmltable. One of the tag returns date and I want to take it in date format in xmltable column.

select t.* from XMLTYPETEST xt ,
XMLTable ('/TestData/trade' passing xt.data
columns
test varchar2(100) path 'GenTest/Id',
testdate date path 'DateTest/Date')t;

But its throwing error as -

SQL Error: ORA-29958: fatal error occurred in the execution of ODCIINDEXCREATE routine
ORA-01830: date format picture ends before converting entire input string
29958. 00000 - "fatal error occurred in the execution of ODCIINDEXCREATE routine"
*Cause:    Failed to successfully execute the ODCIIndexCreate routine.

View 2 Replies View Related

VARCHAR2 (100) Changes Column To VARCHAR2 (100 Byte)

Oct 24, 2007

i am fairly new in the oracle arena, but what would cause a statement such as

ALTER TABLE TEST_TABLE MODIFY text_field1 varchar2(100) DEFAULT 'testval' NULL

to change a column's type from VARCHAR2(100) to VARCHAR2(100 byte)? i found a few mentions of the 100 byte concept online but nothing that jumped out at me.

View 2 Replies View Related

SQL & PL/SQL :: How To Get Date From The Timestamp Data Type

Oct 13, 2011

How can i get just date from the timestamp data type.

Suppose i have a column timestamp with has data like "2011-05-16 16:19:22.579764-07" when i select from table i just want the date like 2011-05-16.

View 6 Replies View Related

Insert Or Select Date Data Type Value In AM Or PM?

Oct 26, 2006

How do i insert or select date data type value in am or pm Eg 10:30:00 am or 10:35:00 pm

View 3 Replies View Related

PL/SQL :: Equals (=) Versus LIKE For Date Data Type

Sep 2, 2013

First, I'm aware that the equals (=) operator is a "comparison operator compares two values for equality."  In other words, in an SQL statement, it won't return true unless both sides of the equation are equal.  For example:

SELECT * FROM Store WHERE Quantity = 200; The LIKE operator "implements a pattern match comparison" that attempts to match "a string value against a pattern string containing wild-card characters." 

For example:

SELECT * FROM Employees WHERE Name LIKE 'Chris%'; 

Here,I query about date type data on ORACLE database, I found the following, when I write select statment in this way:

SELECT ACCOUNT.ACCOUNT_ID, ACCOUNT.LAST_TRANSACTION_DATE FROM ACCOUNT WHERE ACCOUNT.LAST_TRANSACTION_DATE LIKE '30-JUL-07';

I get all rows I'm looking for. but when I use the sign equal =

instead :SELECT ACCOUNT.ACCOUNT_ID, ACCOUNT.LAST_TRANSACTION_DATE FROM ACCOUNT WHERE ACCOUNT.LAST_TRANSACTION_DATE = '30-JUL-07';

I get nothing even though nothing is different except the equal sign.

View 6 Replies View Related

Server Administration :: Rowid And Varchar2 Type

Oct 26, 2011

Rowid type is one of data types in oracle,is it equal to the varchar2,the flowing test show varchar2 type can contain rowid type.

SQL> Declare
2 L_Rowid1 Varchar2(18);
3 L_Rowid2 Rowid;
4 Begin
5

[Code]....

View 2 Replies View Related

SQL & PL/SQL :: Custom Date Or Data Type To Compare Semesters

Jul 26, 2011

I have a table with two columns called startsem and gradsem they are both CHAR(3). Within those colums are rows that are listed as SemesterYear. For example, F09 is Fall 2009, S09 Spring 2009, and M09 is Summer 2009. I would like to create a constraint that says GradSem must be greater than StartSem b/c no one can travel back in time to graduate. However, as you know you can compare S09 > F09 because it will treat it as a string. I thought I could use a substring and compare the last two digits as a year and that would work but how do I compare the semesters as a time frame? Because in my schema F > S because Spring 2011 comes before Fall 2011 but in reality F < S because to Oracle it is a string and the ASCII value of F is less than S. I cannot chage the coding of the database so editing the rows so they are more date friendly is not an option.

So how can I modify this database to acruately compare StartSem and GradSem.

View 7 Replies View Related

How To Name A Column From Data Stored In Certain Row

Oct 28, 2012

I am trying to name a column from data stored in a row (Phone number) I want to name it from the first 3 digits (Area code) and I have no clue how to do it I have googled but I think the query is a little too complicated for me to explain it and get a result from google or find it in a section of a book.

My phone numbers are strings, so I initially look for the first 3 characters and then start separating and counting, I can do that, the only problem I have is naming the column WITH the the first three digits that I just separated.

View 4 Replies View Related

How To Name Column From Data Stored In Certain Row

Oct 28, 2012

I am trying to name a column from data stored in a row (Phone number) I want to name it from the first 3 digits (Area code) and I have no clue how to do it I have Googled but I think the query is a little too complicated for me to explain it and get a result from Google or find it in a section of a book.

My phone numbers are strings, so I initially look for the first 3 characters and then start separating and counting, I can do that, the only problem I have is naming the column WITH the the first three digits that I just separated.

I am trying to learn by myself how to use databases and am using the HR database that comes in oracle since I read in a forum that it is a great way to learn how to use queries.

I read about using Pivot to do it, but in pivot I need to know how many area codes there are. I will post a screen shot of the table that I am working on the other post

[URL].....

I can group by phone number with this:

SELECT SUBSTR(PHONE_NUMBER, 1, 3) AREA, COUNT(SUBSTR(PHONE_NUMBER, 1, 3)) TOTAL_COUNT
FROM EMPLOYEES
GROUP BY SUBSTR(PHONE_NUMBER, 1, 3);

But what I am interested in doing is that instead of Area appearing as a column, that the actual first 3 digits would appear as the column name.

This is kind of what I am trying to do, but instead of AREA appearing as column name I would like for each one of the area codes to appear as column name, here is the

SELECT DE.DEPARTMENT_NAME, EM.SEX, SUBSTR(EM.PHONE_NUMBER, 1, 3) AREA, COUNT(SUBSTR(EM.PHONE_NUMBER, 1, 3)) TOTAL_COUNT FROM EMPLOYEES EM, DEPARTMENTS DE WHERE DE.DEPARTMENT_ID = EM.DEPARTMENT_ID GROUP BY DEPARTMENT_NAME, EM.SEX, SUBSTR(PHONE_NUMBER, 1, 3) ORDER BY DEPARTMENT_NAME;

View 5 Replies View Related

SQL & PL/SQL :: Extracting Data From LONG RAW Data Type Column

Oct 7, 2011

I have come one requirement where i need to extract data from a LONG RAW data type column.

View 7 Replies View Related

SQL & PL/SQL :: What Should Be The Data Type For The Column

May 9, 2012

I have to create a table which should store data at Week level. The table have the following columns

Product id, Loc id, Business group id, FISCAL WEEK , Revenue,

Fiscal week column will have data as '2011-W01', '2011-W47' etc.

What should be the data type for fiscal week column. Based on this table i have to create a calculated column which should fetch trailing 12 weeks average for each row.

View 5 Replies View Related

Server Utilities :: Import Data Type Date From Excel To Oracle

Feb 20, 2013

I have imported data from excel to oracle 11g. But i found an error like

"Ensure format is entered for datatypes 'Date' and 'TIMESTAMP' on data type pane".

after that i try to modify type date in oracle become 'dd-mmm-yyyy'

View 14 Replies View Related

PL/SQL :: Randomize Data Stored In One Column Of A Table

Jul 12, 2012

I have a problem that I don't know how to solve it.here is it:

I have a table test1(intitule varchar,age number):

INTITULE AGE
------------------------------ ----------
nom1 1
nom2 2
nom3 3
nom4 4
nom5 5
nom6 6
nom7 7
nom8 8
nom9 9
nom10 10

I want to disorder(randomize) column INTITULE without touching to 'age'.

View 28 Replies View Related

XML Type Column Data XML Query?

Mar 16, 2013

I have extracted in the following XML document some Mpeg7 visual descriptors from an image and I saved it in an XMLType column. I would like to use XMLQuery to extract the data from the Value node. how write such a query. I could not get the proper Xpath to any node.

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<Mpeg7 xmlns="http://www.mpeg7.org/2001/MPEG-7_Schema" xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance">
<DescriptionUnit xsi:type="DescriptorCollectionType">

[code]...

View 1 Replies View Related

XML DB :: Insert Element Into Data Stored In CLOB Column?

Dec 14, 2012

My ORACLE DB version is:

('Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production');
('PL/SQL Release 11.2.0.2.0 - Production');
('CORE 11.2.0.2.0 Production');
('TNS for Linux: Version 11.2.0.2.0 - Production');
('NLSRTL Version 11.2.0.2.0 - Production');

I have this XML data stored in a CLOB column:

<Activity>
<Changes>     
</Changes>
<Inserts>     
</Inserts>
<Definition>     

[code]....

I need creating an update that will insert another <Activity>SomeGUID</Activity> into the <Spawned> parent.

View 3 Replies View Related

SQL & PL/SQL :: Oracle Database 10g - Data In Column Stored In Different Character Set

Sep 23, 2010

Oracle database version is : 10G.

When we try to query a column from a table the output is shown as "?????". Seems the data in that column is stored in a different character set, which is not recognised while displaying the output. The column size is 35byte.

The nls parameter table shows the character set as "we8mswin1252".

get this issue resolved.

View 7 Replies View Related

PL/SQL :: ORA-02267 / Column Type Incompatible With Referenced Column Type

Oct 23, 2012

i what to update a column data type am geting this error

ORA-02267: column type incompatible with referenced column type

my query is

when i run

SELECT       b.table_name||
' joins to the '||
a.table_name||
' table on '||
c.column_name||
' = '||
d.column_name          Table_relationships

[code]....

View 9 Replies View Related

Updating Long Data Type Column

Aug 31, 2010

I have a task to update one of the rows in a table (having only 2 columns, number and long) which is long data type. We are on Oracle 10g. Not sure how to use update for a long data type column.

I have tried using dbms_metadata_util.long2varchar, but still not getting what I want.

View 2 Replies View Related

SQL & PL/SQL :: Table With VARCAHAR2 Data Type Column

Jul 28, 2010

I have a table with VARCAHAR2 data type column, I am unable to update that column with the following data.

UPDATE we set sname='(([Emp].Job.Catageory
IN 'SALES,COMPUTER,SCIENCE,TELESERVICE')
AND ([Employee].IDtype IN 'ABC'))'

I am getting the error "ORA-00933: SQL command not properly ended"

View 9 Replies View Related







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