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

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.
chandra sekharAsked:
Who is Participating?
 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
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
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Try

--

SELECT * FROM table

WHERE 

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

--

Open in new window


--
0
 
Rgonzo1971Connect With a Mentor Commented:
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
 
chandra sekharAuthor Commented:
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
 
Rgonzo1971Connect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.