@sri vs​ maybe concatenate the two strings and use to_timestamp with the format specified?

https://docs.databricks.com/spark/latest/spark-sql/language-manual/functions/to_timestamp.html

https://docs.databricks.com/spark/latest/spark-sql/language-manual/functions/concat.html

creating timestamp:

spark.sql("select to_timestamp(concat('20221009','010911'),'yyyyMMddhhmmss') as my_date").display()

 formatted with AM /PM:

spark.sql("select date_format(to_timestamp(concat('20221009','010911'),'yyyyMMddhhmmss'),'yyyy-MM-dd hh:mm:ss a') as my_date").display()