Solved

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

Posted on 2016-09-20
6
34 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 18

Assisted Solution

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

--

SELECT * FROM table

WHERE 

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

--

Open in new window


--
0
 
LVL 48

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 48

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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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.

744 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now