Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 66
  • Last Modified:

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.
0
chandra sekhar
Asked:
chandra sekhar
4 Solutions
 
Pawan KumarDatabase ExpertCommented:
Try

--

SELECT * FROM table

WHERE 

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

--

Open in new window


--
0
 
Rgonzo1971Commented:
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
 
Rgonzo1971Commented:
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
 
Mark GeerlingsDatabase 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

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now