Avatar of Maliki Hassani
Maliki Hassani
Flag for United States of America 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".
Oracle DatabaseSQL

Avatar of undefined
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.
SOLUTION
slightwv (䄆 Netminder)

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Maliki Hassani

ASKER
Yes,  I will have to get with the admin..  Thank you at least it works for now.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Sean Stuber

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.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.