Does Jonathan's (excellent) response solve your problem?

+1, this has the added benefit...

Put the date into YYYYMMDD format before casting and it should work.

DECLARE @x VARCHAR(100) = '09142023';

SELECT @x
      ,SomeDate = CAST (CONCAT (RIGHT(@x,...

Here is a possible approach.

1. Get the code for your DB into a VS database project and check that into a DevOps Git repo.
2. Set the database project to target...

If you would provide just 10 rows of sample data, in consumable format, it would help a lot.

Presumably, your question is not this:

"Why are no results returned when I join on two columns containing values which do not match?"

What is your real question?

The issue is, all of these tables dont have primary/secondary keys, which i brought in with sl code.

Because these keys dont correspond to one another it gives me these...

Like this?

WITH ordered
AS (SELECT *
          ,rn = ROW_NUMBER () OVER (PARTITION BY adc.channelid ORDER BY adc.recordcount DESC)
 ...

You seem to have a lot of Postgres problems ... why not find a Postgres forum?

There is a button that says 'Code' in your icon bar. With >3,000 points, you...