We help IT Professionals succeed at work.

Oracle: Date coversion for joining tables

1,490 Views
Last Modified: 2014-05-13
Experts,

I have 2 tables that I am trying to join.  I am using date fields to join on. The problem is that the fields are formatted differently.  What would be the best way to format them so I can join?  Here are the outputs:

Table 1
field name: report_date_run
5/12/2014 8:17:06 AM

Table 2
field name: report_date
12-MAY-14

I am thinking I would convert table 1 to match table 2 "Report_date".
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Dates in Oracle don't have a format.

Are the fields actually a DATE data type or a varchar2?

The one in table 2 looks like the default Oracle date format so I assume it is an actual date

If table1 is a varchar2, just convert it to date:
trunc(to_date('5/12/2014 8:17:06 AM','MM/DD/YYYY HH:MI:SS AM'))

The trunc is to strip off the time.

You may need that on the date in table2 as well.

Author

Commented:
Yes, they are both varchar2

Table 1 output now is: 5/12/2014 12:00:00 AM

Table 2 remains the same since it doesn't have a date format.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Yes,  I will have to get with the admin..  Thank you at least it works for now.
Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.