Solved

SQL query help

Posted on 2014-03-03
2
307 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
2 Comments
 
LVL 73

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

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in 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.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

920 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

11 Experts available now in Live!

Get 1:1 Help Now