• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

SQL query help

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
Perumal_RM
Asked:
Perumal_RM
1 Solution
 
sdstuberCommented:
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
 
Perumal_RMAuthor Commented:
Works like a charm. I have another one there too. that is to append some date and time to a string.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now