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
Solved

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

Posted on 2016-09-17
4
41 Views
Last Modified: 2016-11-02
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
Comment
Question by:chandra sekhar
  • 2
4 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41802703
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
 

Author Comment

by:chandra sekhar
ID: 41802722
Hi,

Data type is Varchar

Note:-please find attachment in txt format
Sample_data.txt
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 41803484
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

860 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