Solved

How to successfully convert date in Oracle

Posted on 2015-01-07
14
313 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 34

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 34

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 34

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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
database upgrade 8 79
clob to char in oracle 3 42
Oracle DBLINKS From 11g to 8i 3 35
Field name with special character (Ñ) in Oracle 11 51
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

803 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