Solved

How to retrive data which is having only DD/MM/YYYY format

Posted on 2016-09-20
6
51 Views
Last Modified: 2016-11-05
My table having one varchar2 column,which is having all type of data formats inserted to into this table.

Now,i want to retrieve data which is records having DD/MM/YYYY format

please let me know,how to write.
0
Comment
Question by:chandra sekhar
6 Comments
 
LVL 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 125 total points
ID: 41806262
Try

--

SELECT * FROM table

WHERE 

ISDATE(FORMAT(Date,'DD/MM/YYYY') = 1)

--

Open in new window


--
0
 
LVL 50

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 250 total points
ID: 41806275
Hi,

pls try

SELECT myField, *
FROM myTable where regexp_like(myField,'\d\d/\d\d/\d\d\d\d')

Open in new window

Regards
0
 

Author Comment

by:chandra sekhar
ID: 41806299
Hi,

My table having below date formats also

MM/DD/YYYY
DD/MM/YYYY

but your query retriving both formts ,need only DD/MM/YYYY format
0
 
LVL 50

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 250 total points
ID: 41806307
Hi,

if the date is 11/11/2016 it cannot know which format

to restrict a little bit
SELECT val
FROM org where regexp_like(val,'(0[1-9]|[1][0-2])/(0[1-9]|[1|2][0-9]|[3][0|1])/\d\d\d\d')

Open in new window

0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 125 total points
ID: 41806632
If the data is stored in a variety of formats in a VARCHAR2 column, there is no easy way to retrieve only the values that can be converted to valid dates.

Yes, you can use some combinations of regexp_like and/or substr to identify data patterns that a human can interpret as a date.  But, getting a query to handle a variety of data formats consistently will be a challenge!  That is why "date" values should be stored in Oracle "date" columns.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question