?
Solved

SQL query help

Posted on 2014-03-03
7
Medium Priority
?
152 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
More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

801 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