Solved

SQL query help

Posted on 2014-03-03
7
150 Views
Last Modified: 2014-04-02
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

I used the below suggested

update source_trim set date_of_birth_1 = to_date(date_of_birth,'dd/mm/yyyy HH:MI AM')
where regexp_like(date_of_birth,'(0[1-9]|[12][0-9]|30|31)/(0[1-9]|1[12])/[0-9]{4}');

It updates the values for example 19/MAY/75, what i want is to append 12:00 AM as well. If its present in col1, i would use that, else I might simply append 12:00 AM.

How do i do that.
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
  • 3
  • 2
  • 2
7 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39902496
since col2 is a date,  the 12:00am  or 00:00 is already part of the value.

There is nothing more to add.


Whenever a DATE value doesn't have time specified it always defaults to 00:00:00  (12am)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39902502
If you're trying to see the time portion of your date then you need to format it into text

for example:


to_char(col2,'yyyy-mm-dd hh24:mi:ss')

or

to_char(col2,'dd/mm/yyyy hh:mi AM')
0
 

Author Comment

by:Perumal_RM
ID: 39902504
My col2 is varchar.

So its better i append those...
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39902598
posting my comment from your duplicate question:
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_28379519.html

udpate yourtable 
  set col2 = case when col1 like '[0-3][0-9]/[0-1][0-9]/[0-9][0-9][0-9][0-9]% 
then replace(col1 , ' at ', ' ' )
else null end  

Open in new window

0
 

Author Comment

by:Perumal_RM
ID: 39905009
Didnt worked. Pasting my original query here

update source_trim
set last_action_date_1 = case when last_action_date like '[0-3][0-9]/[0-1][0-9]/[0-9][0-9][0-9][0-9]%
then replace(last_action_date,' at ',' ')
else null end;

Error starting at line 3 in command:
update source_trim  
set last_action_date_1 = case when last_action_date like '[0-3][0-9]/[0-1][0-9]/[0-9][0-9][0-9][0-9]%  
then replace(last_action_date,' at ',' ')
else null end;
Error at Command Line:5 Column:32
Error report:
SQL Error: ORA-00905: missing keyword
00905. 00000 -  "missing keyword"
*Cause:    
*Action:
0
 

Author Comment

by:Perumal_RM
ID: 39905201
update source_trim
set last_action_date_1=replace(last_action_date,' at ',' ');

I used this and it worked...
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39906593
sorry, a closing quote was missing:
update source_trim
set last_action_date_1 = case when last_action_date like '[0-3][0-9]/[0-1][0-9]/[0-9][0-9][0-9][0-9]%'
then replace(last_action_date,' at ',' ')
else null end;

Open in new window

0

Featured Post

Technology Partners: 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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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 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 how to recover a database from a user managed backup

724 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