• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

How to successfully convert date in Oracle

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
newtoperlpgm
Asked:
newtoperlpgm
  • 5
  • 4
  • 4
  • +1
2 Solutions
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
newtoperlpgmAuthor Commented:
I apologize, both dates are varchar2, neither are dates.
0
 
johnsoneSenior Oracle DBACommented:
What is the format of the date in the 2 columns?  Depending on the format a conversion to date may not be necessary.
0
 
newtoperlpgmAuthor Commented:
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
 
newtoperlpgmAuthor Commented:
Johnone both columns have the following date format. 09/24/2012 12:36:02
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
newtoperlpgmAuthor Commented:
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
 
johnsoneSenior Oracle DBACommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now