[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to successfully convert date in Oracle

Posted on 2015-01-07
14
Medium Priority
?
330 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 14

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

649 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