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
8/22/2022 - Mon
slightwv (䄆 Netminder)
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.
Maliki Hassani
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.
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.