Solved

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

Posted on 2016-09-17
4
31 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle TEXT search question 9 45
Oracle -- identify blocking session 24 41
database upgrade 8 37
PL/SQL Display based on value 4 16
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

929 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now