Joining tables with different date formats (Literal does not match format string)

Question

I'm trying to join two Oracle tables (basically to pull in a daily rate against the date in another table), but the table I'm pulling the date from has the timestamp in it. I'm seeing a lot of solutions for the "Literal does not match format string" problems, but they all seem to be for entering a date directly instead of joining from another table. Apologies if I didn't understand the earlier solutions correctly, but I'm very novice to SQL. My code currently is basically:

select gdr.conversion_rate as "Rate", pa.agreement_date as "agreement Rate Date"
 from apps.gl_daily_rates gdr, apps.project_agreements pa    
where gdr.rate_date = pa.agreement_date

With the pa.agreement_date value being the one with the extra times stamp in it. I tried using the todate function around the pa.agreement_date in the join but was definitely not doing it correctly, if that's the proper solution. Thanks!

Answer 1

DATE datatype in Oracle containes 7 bytes (for century, year, month, day, hour, minute and second) and there is ALWAYS a time component contained. That is not a timestamp cause the TIMESTAMP in Oracle is a separate datatype.

When you join tables on any columns be sure that the left and right side of the condition are of the same type. Doing so, when values (of the same datatype) match then you have joined rows of data from both tables.

When dealing with dates it is best to have DATE datatype. When that is not the case (one column is DATE the other VARCHAR2) convert the non-date column to DATE keeping in mind time component of the datatype (default is 00:00:00).

Never use To_Date() function on data that already is of DATE datatype.

Here is the code to get your result with different join ON options.

Additional comments in the code:

WITH    --  S a m p l e    D a t a :
   gl_daily_rates (RATE_DATE, CONVERSION_RATE) AS
    ( Select To_Date('2001-05-21', 'yyyy-mm-dd'), 1 From Dual Union All
      Select To_Date('2001-05-22', 'yyyy-mm-dd'), 2 From Dual Union All
      Select To_Date('2001-05-23', 'yyyy-mm-dd'), 3 From Dual Union All
      Select To_Date('2001-05-24', 'yyyy-mm-dd'), 4 From Dual Union All
      Select To_Date('2001-05-25', 'yyyy-mm-dd'), 5 From Dual Union All
      Select To_Date('2001-05-29', 'yyyy-mm-dd'), 6 From Dual Union All
      Select To_Date('2001-05-30', 'yyyy-mm-dd'), 7 From Dual Union All
      Select To_Date('2001-05-31', 'yyyy-mm-dd'), 8 From Dual Union All
      Select To_Date('2009-05-13', 'yyyy-mm-dd'), 9 From Dual 
    ),
  project_agreements (AGREEMENT_DATE) AS
    ( Select '2010/06/30 00:00:00' From Dual Union All
      Select '2010/04/26 00:00:00' From Dual Union All
      Select '2010/04/23 00:00:00' From Dual Union All
      Select '2008/10/31 00:00:00' From Dual Union All
      Select '2008/10/31 00:00:00' From Dual Union All
      Select '2009/05/13 00:00:00' From Dual
    )

--    S Q L :
Select      gdr.CONVERSION_RATE "Rate", 
            pa.AGREEMENT_DATE "Agreement_Rate_Date"
From        gl_daily_rates gdr
Left Join   project_agreements pa 
    -- Different options for ON clause condition (dates are prefered)
    -- use just one of them, ofcourse
         -- ON   DATE datatype       (1)  works with sample data cause time for both columns are all 00:00:00
            ON( To_Date(pa.AGREEMENT_DATE, 'yyyy/mm/dd hh24:mi:ss') = gdr.RATE_DATE  )
         
         -- ON   DATE datatype       (2)  -- when joining on dates regardless time component
            ON( TRUNC(To_Date(pa.AGREEMENT_DATE, 'yyyy/mm/dd hh24:mi:ss')) = TRUNC(gdr.RATE_DATE  ))
            
         -- ON   VARCHAR2  datatype
            ON( SubStr(pa.AGREEMENT_DATE, 1, 10) = To_Char(gdr.RATE_DATE, 'yyyy/mm/dd'  ) )
          
         -- ON   NUMBER  datatype
            ON( To_Number(Replace(SubStr(pa.AGREEMENT_DATE, 1, 10), '/', '')) = To_Number(To_Char(gdr.RATE_DATE, 'yyyymmdd'  )) )
Order By    gdr.RATE_DATE

The above SQL uses Left (Outer) Join to see all the rates and just matching Agreement_Rate_Date:

/*    R e s u l t :    (Left Join)
Rate    Agreement_Rate_Date
----    -------------------
   1    null
   2    null
   3    null
   4    null
   5    null
   6    null
   7    null
   8    null                   
   9    2009/05/13 00:00:00  */

... if you want to see just matching rows use an Inner Join instead

/*    R e s u l t :    (Inner Join)
Rate    Agreement_Rate_Date
----    -------------------
   9    2009/05/13 00:00:00  */

See the fiddle here.

NOTE

Do not use list of tables syntax as it was deprecated for 30+ years now - use Join ON syntax.

Answer 2

Based on the comments you have agreement_date as a VARCHAR, and rate_date as a DATE. In this case you need to cast to date only a VARCHAR column.

SELECT
  gdr.conversion_rate as "Rate",
  pa.agreement_date as "agreement Rate Date"
FROM apps.gl_daily_rates gdr
JOIN apps.project_agreements pa
  ON gdr.rate_date = TO_DATE(pa.agreement_date, 'YYYY/MM/DD HH24:MI:SS') 

Side note, I believe it's better to avoid adding several tables after the FROM, and use JOIN instead.

Answer 3

You appear to have two tables, one with a DATE column and one with a VARCHAR2(150) column:

CREATE TABLE apps.gl_daily_rates(rate_date DATE, conversion_rate NUMBER);

CREATE TABLE apps.project_agreements (agreement_date VARCHAR2(150));

and the sample data from your comment:

INSERT INTO apps.gl_daily_rates (rate_date, conversion_rate)
SELECT DATE '2001-05-21', 1 FROM DUAL UNION ALL
SELECT DATE '2001-05-22', 2 FROM DUAL UNION ALL
SELECT DATE '2001-05-23', 3 FROM DUAL UNION ALL
SELECT DATE '2001-05-24', 4 FROM DUAL UNION ALL
SELECT DATE '2001-05-25', 5 FROM DUAL UNION ALL
SELECT DATE '2001-05-29', 6 FROM DUAL UNION ALL
SELECT DATE '2001-05-30', 7 FROM DUAL UNION ALL
SELECT DATE '2001-05-31', 8 FROM DUAL;

INSERT INTO apps.project_agreements (agreement_date)
SELECT '2010/06/30 00:00:00' FROM DUAL UNION ALL
SELECT '2010/04/26 00:00:00' FROM DUAL UNION ALL
SELECT '2010/04/23 00:00:00' FROM DUAL UNION ALL
SELECT '2008/10/31 00:00:00' FROM DUAL UNION ALL
SELECT '2008/10/31 00:00:00' FROM DUAL UNION ALL
SELECT '2009/05/13 00:00:00' FROM DUAL

Then when you run your query:

SELECT gdr.conversion_rate as "Rate",
       pa.agreement_date as "agreement Rate Date"
FROM   apps.gl_daily_rates gdr
       INNER JOIN apps.project_agreements pa    
       ON gdr.rate_date = TO_DATE(pa.agreement_date, 'YYYY/MM/DD HH24:MI:SS')

It "works" but produces no output. This is because the dates you are joining on do not match.

If you have dates that do match into the table:

INSERT INTO apps.project_agreements (agreement_date)
SELECT '2001/05/21 00:00:00' FROM DUAL UNION ALL
SELECT '2001/05/22 00:00:00' FROM DUAL UNION ALL
SELECT '2001/05/23 00:00:00' FROM DUAL UNION ALL
SELECT '2001/05/24 00:00:00' FROM DUAL UNION ALL
SELECT '2001/05/25 00:00:00' FROM DUAL;

and re-run the SELECT query then your query will work and generate the output:

Rate	agreement Rate Date
1	2001-05-21 00:00:00.000000
2	2001-05-22 00:00:00.000000
3	2001-05-23 00:00:00.000000
4	2001-05-24 00:00:00.000000
5	2001-05-25 00:00:00.000000

fiddle