asked on 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".
Oracle DatabaseSQL
Last Comment
Sean Stuber
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Yes, I will have to get with the admin.. Thank you at least it works for now.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.