Solved

How to successfully convert date in Oracle

Posted on 2015-01-07
14
317 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
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!

 
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

726 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