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


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

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 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 :: Use Of Order By In Varchar2

Feb 21, 2011

I have a column named "col1" with datatype "varchar2(10)" and row wise entries like "1,1A, 2,3...,10,2A,..." like. I want to order it like "1, 1A ,2,2A, 2B,3... 10...".I tried it with to_number() but it gives me

1,10,11,2,....like that.

View 14 Replies View Related

SQL & PL/SQL :: Order By For VARCHAR2

Sep 28, 2010

create table members(
memberID varchar2(7),
locationID number(5),
roomID varchar2(5)
)
/

[Code]....

MEMBERI LOCATIONID ROOMI
------- ---------- -----
2007209 1 1
2006253 1 1-A
2006253 1 1-A
2006265 1 1-B
2006240 1 1-C
2005064 1 1-D
2007836 1 100
2007211 1 101
2007075 1 102
2007110 1 105

[Code]...

Function created.

SQL> select * from members
2 order by safe_to_number(roomID);

MEMBERI LOCATIONID ROOMI
------- ---------- -----
2007209 1 1
2007185 1 2
2007089 1 3
2007023 1 4

[Code]....

How can I sort the output to get the result like below

2007209 1 1
2006253 1 1-A
2006253 1 1-A

[Code]...

and so on...

View 13 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 :: 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

SQL & PL/SQL :: Convert Varchar2 To Date Or Number

Jun 18, 2011

I created table Contains then following columns

cheq varchar2(50)
date_due varchar2(50)

and data entry in this columns

cheq 500,1500,5000 all values numbers in this columns

date_due 1-1-2012 , 15-9-2010 all values in this columns date

i want sum the column "cheq"

when used this code but it's not working

select sum(to_number(cheq))
from table_name but the code not working

second column "date_due"

i want search between to date i used this code but also not working

select cloumn1,cloum2
from table_name
where to_char(date_due,'dd-mm-yyyy')
between to_char(date_due,'dd-mm-yyyy') and
(date_due,'dd-mm-yyyy')

but not work

View 13 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 :: Order By In Date Column

May 20, 2013

I am trying group by count on Monthly basis, entered_timestamp Column is of DATE datatype.

select count(*),to_char(entered_timestamp,'MM-YYYY') MONTHLY from payments
where type = 'BOOK' group by to_char(entered_timestamp,'MM-YYYY') order by MONTHLY DESC

Above SQL doesn't gives the output in proper order ,

View 3 Replies View Related

SQL & PL/SQL :: Order By DATE Which Has NULL Values

Jun 11, 2010

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

I have Event_month, Event_date in SELECT statement. Results of the query has NULL values as well.. I am doing ORDER BY Event_month, and I want NULL values to be at the top..

SELECT d.event_code_dt, count(d.event_number) count1
FROM main_event a,sec_event d
WHERE a.event_id = d.event_id(+)
GROUP BY d.event_code_dt
ORDER BY d.event_code_dt, count(d.event_number) desc

Results are like this

11-MAY-10
21-MAY-10
22-MAY-10
NULL

And I want results to be like this:
NULL
11-MAY-10
21-MAY-10
22-MAY-10

Same thing happens with Event_month
NULL
Feb 2009
Aug 2009

View 2 Replies View Related

PL/SQL :: Order By In Union Clause In Date Column

Nov 3, 2012

Oracle version : 11.2.0.2 Linux EL6 server

I have a query like below:

select col1, col2, col3, col4
from tab1, tab2, tab3
where conditions
union

[Code]...

Now, the col4 is a date column and I have to order by the entire result sets on it. I know I can do it by (order by col4) or by (order by 4) at the end of the entire query.

But the problem is that, the output is coming in dd-Mon-yyyy (i.e 31-Nov-2012).

I want every output in dd/mm/yyyy format so I need to use to_char function.

But in that case, I cant use the order by clause, because in that case it is getting arranged by character i.e by 1,2,3,4,5 like this.

View 3 Replies View Related

PL/SQL :: Order By Date Column With A Format Mask

May 16, 2013

10g- 10.2.0

This is my query

select to_char(exp_Date,'Mon-YYYY') dt, count(*) from exp_main
where exp_type like 'Income%Photo%'
group by to_char(exp_Date,'Mon-YYYY')
order by exp_date

When I run this I get :not a GROUP BY expression

If I remove the order by, it works fine.

Is there a way to order by since the output of the query is character-sorted.

I did search online most have suggested to use order by column_name. But does not work for me.

View 6 Replies View Related

SQL & PL/SQL :: Date Format - Do (ORDER BY) By Concatenating Two Columns?

Jun 26, 2013

I want to do 'ORDER BY' by concatenating the two columns(date +varchar2).But not working and geting the error - ORA-01855: AM/A.M. or PM/P.M. required

SQL> create table dat2 (
mod_date date,
am_pm varchar2 (10) ) ; 2 3

Table created.
SQL> INSERT INTO DAT2 ( MOD_DATE, AM_PM ) VALUES (
TO_Date( '06/05/2003 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '7:25AM'); 2

1 row created.

[code]...

ERROR at line 2:
ORA-01855: AM/A.M. or PM/P.M. required

View 25 Replies View Related

Forms :: Add The JCalendar.jar And JCalendarJinit.jar In Order To Use Date Picker?

Mar 29, 2011

i want to add the JCalendar.jar and JCalendarJinit.jar in order to use the date picker on my forms while using a windows environment,what attributes should i change or edit in the forms/server/formsweb.cfg file and what else am i required to add?

View 1 Replies View Related

Forms :: How To Make A Date Field Filter In Descending Order

Aug 5, 2011

I have a question regarding a Date field on one of my form.

How do I make a Date field filter in a descending order when the form is opened? Also, can I add a 'when-mouse-doubleclick' trigger to sort the date field in ascending and descending order (this is a client requirement)?

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 :: Changing DATE Type?

Oct 25, 2011

oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

In the database, NLS_DATE_FORMAT = DD-MON-RR.I have a date field called LOAD_DATE in one of the tables. I want to store the value as '25-OCT-11 01:16:25 PM' in LOAD_DATE field. Is there a way to do it without changing the NLS parameter (ALTER SESSION...) ?

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

SQL & PL/SQL :: Difference Between Two Date Type Fields

Aug 15, 2011

I'm having trouble comparing and subtract two date type fields, can not get a significant result.

e.g.

select id, date.started, date.closed, datediff (day, date.started, date.closed) differences diference from Table1 order by desc;

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

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

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

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

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

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

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

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







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