• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 162
  • 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

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
Perumal_RM
Asked:
Perumal_RM
  • 3
  • 2
  • 2
1 Solution
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
Perumal_RMAuthor Commented:
My col2 is varchar.

So its better i append those...
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Perumal_RMAuthor Commented:
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
 
Perumal_RMAuthor Commented:
update source_trim
set last_action_date_1=replace(last_action_date,' at ',' ');

I used this and it worked...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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