How Can I extract last 8 characters of a string which represent the date in this format 'YYYYMMDD' and convert into date?

From your question, it is not really clear where the data is stored and what tools you are using to access the data. Depending on where the data is available (Redshift vs RDS vs S3 vs Kinesis etc) and depending on the tool used to query the data, the answer would change.

I will list a couple of ways you can accomplish this:

1. In Athena when the data is cataloged in Glue tables (Eg. the data is in S3): you could execute this query - select cast(date_parse('20220915','%Y%m%d') as date);
2. In Glue when you are accessing the data as a Glue dynamicFrame or Pyspark dataframe:

from pyspark.sql.functions import *
#df is the dataframe that you add logic here to read/transform or convert from Glue Dynamicframe
df.select(col("input"),to_date(col("input"),"yyyyMMdd").alias("date_column")).show()

3. In Redshit: select to_date('20220915', 'YYYYMMDD', FALSE);

Reference: https://docs.aws.amazon.com/redshift/latest/dg/r_TO_DATE_function.html