Solved

SQL query help

Posted on 2014-03-03
2
321 Views
Last Modified: 2014-03-03
I have one table with two cols

Table1
-----------
col1                                                                  col2
-----                                                                  -----
",,"                  
(null)            
19/05/1975
27/08/1976
MK109 - Sales Training (All Staff)


What I want is to
1. update col2 with date format
2. If there is any data other than dd/mm/yyyy format (regex) make it to null

How do i do that.

I started off but couldnt complete...

update Table1 set col2=(select to_char(mydate,'DD-MON-RR HH:MI') from (
select to_date(col1,'DD-MM-RRRR') mydate from Table1) where (rowid=Table1.rowid and col1 != null));

The above query updates every row to null...

Can you guys help me?
0
Comment
Question by:Perumal_RM
[X]
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
2 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39902464
update table1 set col2 = to_date(col1,'dd/mm/yyyy')
where regexp_like(col1,'(0?[1-9]|[12][0-9]|3[01])/(0?[1-9]|1[012])/[0-9]{1,4}');


Note, this checks for the correct form and most reasonable ranges; but it's not a completely reliable date checker.


31/06/2014   would be considered a legal date string even though June doesn't have 31 days

29/02/2013 would also be considered legal, even thought 2013 isn't a leap year
0
 

Author Closing Comment

by:Perumal_RM
ID: 39902488
Works like a charm. I have another one there too. that is to append some date and time to a string.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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ā€¦
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

734 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