Oracle: Date coversion for joining tables
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".
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".
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes, I will have to get with the admin.. Thank you at least it works for now.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.