How to find out date formats,which are extra in my table apart from below date formats?

WHEN REGEXP_LIKE (HIRE_DATE,'^[0-9]{1,2}/[0-9]{1,2}/[0-9]{4}$') THEN TO_CHAR(TRUNC(TO_DATE(HIRE_DATE,'MM/DD/RRRR')))
       WHEN REGEXP_LIKE (HIRE_DATE,'^[0-9]{1,2}-[0-9]{1,2}-[0-9]{2,4}$') THEN TO_CHAR(TRUNC(TO_DATE(HIRE_DATE,'DD-MM-RRRR')))
       WHEN REGEXP_LIKE (HIRE_DATE,'^[0-9]{1,2}-[a-zA-Z]{3}-[0-9]{2,4}$','i') THEN TO_CHAR(TRUNC(TO_DATE(HIRE_DATE,'DD-Mon-RRRR')) )
       WHEN REGEXP_LIKE (HIRE_DATE,'^[0-9]{2,4}-[0-9]{1,2}-[0-9]{1,2}$') THEN TO_CHAR(TRUNC(TO_DATE(HIRE_DATE,'RRRR-MM-DD')))
       WHEN REGEXP_LIKE (HIRE_DATE,'^[0-9]{4}/[0-9]{1,2}/[0-9]{1,2}$') THEN TO_CHAR(TRUNC(TO_DATE(HIRE_DATE,'RRRR/MM/DD')))

Note:- Please write a query to find out extra date formats which are available in my table,

please find attached sheet having date formats as well as numbers also,please verify only date related information.
Table-data.xlsx
chandra sekharAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
This will produce a distinct list the formats that are used:

select
  TRANSLATE(x.dt,'0123456789', '##########')
from (
    select '32880' as dt from dual union all
    select '2007-07-19' as dt from dual union all
    select '7/19/2007' as dt from dual union all
    select '07-19-2007' as dt from dual union all
    select '17-JUL-2007' as dt from dual union all
    select '17-09-2007' as dt from dual union all
    select '20/10/2008' as dt from dual
    ) x

Open in new window

The result will look like this:
#####
####-##-##
#/##/####
##-##-####
##-JUL-####
##-##-####
##/##/####

Open in new window

you could cout the occurences if needed by using group by instead of distinct. Hope this helps.
0
 
PortletPaulfreelancerCommented:
What is the data type of the columns in your table?

DATE?
TIMESTAMP?
VARCHAR?


a spreadsheet file is NOT a good way to show us what data information you are storing. Plain text or csv would be better because when Excel opens that file it applies all sorts of logic to the data (including user preferences for date formats and regional settings like dd-mm-yyyy or mm-dd-yyyy)
0
 
chandra sekharAuthor Commented:
Hi,

Data type is Varchar

Note:-please find attachment in txt format
Sample_data.txt
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.