Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2016-09-20
6
Medium Priority
?
64 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 32

Assisted Solution

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

--

SELECT * FROM table

WHERE 

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

--

Open in new window


--
0
 
LVL 53

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 1000 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 53

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 1000 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 500 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

618 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