Solved

SQL query help

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

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 73

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
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.

 
LVL 142

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 142

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

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

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

910 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

24 Experts available now in Live!

Get 1:1 Help Now