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
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Yes, I will have to get with the admin.. Thank you at least it works for now.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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.