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".
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.
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.
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.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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.