Solved

How to successfully convert date in Oracle

Posted on 2015-01-07
14
321 Views
Last Modified: 2015-01-15
Hello,
I need to change the following query to include a conversion of the date from varchar2 to a date. i  tried to_date but get not a valid month.  Thanks for any help.

SELECT * FROM TABLEA A
where a.bc = '290'
and ld_date = (select MAX(LD_DATE)
                   FROM TABLEC C
                   WHERE A.BC = C.BC
                   AND A.SUBJECT = C.SUBJECT);
0
Comment
Question by:newtoperlpgm
[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
  • 5
  • 4
  • 4
  • +1
14 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 40536909
in which table is the date field date data type and varchar2 data type ?

try :

assuming ld_date is date in tablea and ld_date in tablec is varchar2

SELECT * FROM TABLEA A
 where a.bc = '290'
 and ld_date = (select trunc(MAX(to_date(LD_DATE,'dd/mm/yyyy hh24:mi:ss')))
                    FROM TABLEC C
                    WHERE A.BC = C.BC
                    AND A.SUBJECT = C.SUBJECT);
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 40536913
if it is the vice versa case of tablea ld_date is varchar2 and tablec ld_date is date, then try the below :

SELECT * FROM TABLEA A
  where a.bc = '290'
  and trunc(to_date(ld_date,'dd/mm/yyyy hh24:mi:ss') = (select trunc(MAX(LD_DATE))
                     FROM TABLEC C
                     WHERE A.BC = C.BC
                     AND A.SUBJECT = C.SUBJECT);
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40536934
and trunc(to_date(ld_date,'dd/mm/yyyy hh24:mi:ss') = 

Open in new window

But remember: if there is an index for "ld_date", this index will be rendered unusable due to the use of a function upon that column (unless there is a FBI on trunc(....) )

What are the data types of the columns "ld_date" anyways?!
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 40536944
typo in my previous post for the 2nd query I had post, try this :

if it is the vice versa case of tablea ld_date is varchar2 and tablec ld_date is date, then try the below :

 SELECT * FROM TABLEA A
   where a.bc = '290'
   and trunc(to_date(ld_date,'dd/mm/yyyy hh24:mi:ss')) = (select trunc(MAX(LD_DATE))
                      FROM TABLEC C
                      WHERE A.BC = C.BC
                      AND A.SUBJECT = C.SUBJECT);
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 40536949
also please note that if your varchar2 data in one of the ld_date columns is storing time along with the date, then the queries should work fine without the trunc() function. I mean if you want to include the time portion as well along with the date while comparing then you can try either of these...

basically i have removed trunc(..) in the queries.

SELECT * FROM TABLEA A
  where a.bc = '290'
  and ld_date = (select MAX(to_date(LD_DATE,'dd/mm/yyyy hh24:mi:ss'))
                     FROM TABLEC C
                     WHERE A.BC = C.BC
                     AND A.SUBJECT = C.SUBJECT);

SELECT * FROM TABLEA A
   where a.bc = '290'
   and to_date(ld_date,'dd/mm/yyyy hh24:mi:ss') = (select MAX(LD_DATE)
                      FROM TABLEC C
                      WHERE A.BC = C.BC
                      AND A.SUBJECT = C.SUBJECT);
0
 
LVL 35

Expert Comment

by:johnsone
ID: 40537684
The "not a valid month" message comes from one of two places.  Either the date format you are using on the TO_DATE call is incorrect, or you have bad data.
0
 

Author Comment

by:newtoperlpgm
ID: 40545091
I apologize, both dates are varchar2, neither are dates.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 40545283
What is the format of the date in the 2 columns?  Depending on the format a conversion to date may not be necessary.
0
 

Author Comment

by:newtoperlpgm
ID: 40545499
I was able to use
where
to_date(ld_date,'dd/mm/yyyy hh24:mi:ss') = (select MAX(TO_DATE(LD_DATE, 'MM/DD/YY HH24:MI:SS'))
0
 

Author Comment

by:newtoperlpgm
ID: 40545518
Johnone both columns have the following date format. 09/24/2012 12:36:02
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 500 total points
ID: 40545846
ok.. if both are varchar2 columns in this format  09/24/2012 12:36:02

then try this to see if it works :

where to_date(ld_date,'mm/dd/yyyy hh24:mi:ss') = (select MAX(TO_DATE(LD_DATE, 'MM/DD/YY HH24:MI:SS'))
0
 
LVL 35

Expert Comment

by:johnsone
ID: 40546327
If there is an index, I would prefer to use this query:
SELECT * 
FROM   tablea A 
WHERE  a.bc = '290' 
       AND ld_date = To_char((SELECT Max(To_date(ld_date, 
                                         'mm/dd/yyyy hh24:mi:ss')) 
                              FROM   tablec C 
                              WHERE  A.bc = C.bc 
                                     AND A.subject = C.subject), 
                     'mm/dd/yyyy hh24:mi:ss'); 

Open in new window

However, if there is no index on TABLEA(LD_DATE), then it doesn't matter either way.
0
 

Author Comment

by:newtoperlpgm
ID: 40547096
johnsone I am curious about your comment that depending on the format a conversion to date may not be necessary, can you please provide an example of more details.  Thank you.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 40547224
You wouldn't need to convert from string to date if when you sort the strings they would appear in date order.  In order for that to happen, the date format would need to be yyyymmddhh24miss, or some variant of that (the date elements need to appear in that order).
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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 how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

691 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