Solved

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

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

Suggested Solutions

Title # Comments Views Activity
VB.Net - Oracle BulkCopy from CSV Date Format 7 61
Use of Exception to end a Loop 3 34
use lov values 2 50
Oracle - Query link database loop 8 39
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

832 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