• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 69
  • Last Modified:

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
0
chandra sekhar
Asked:
chandra sekhar
  • 2
1 Solution
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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