string date search - previous month

Question

string date search - previous month

arkiboys 9,711

In pyspark databricks I am loading data into delta table...
One of the columns is HireDate which comes in as string, i.e. 20220530
20220421
20220607
...
Question,
What is the select query to return all rows but for previous month
i.e. where HireDate >= 1st of previous month and HireDate < = last day of previous month

Thank you

HimanshuSinha 19,637 Reputation points Microsoft Employee Moderator

2022-06-08T01:07:08.347+00:00
Hello @arkiboys ,
Thanks for the question and using MS Q&A platform.
As we understand the ask here is how to convert a string to date format so that you can perform date function on the same , please do let us know if its not accurate.

The challenage here is that the date eg 20220421 is not a valid date format and so you cannot use any date function on this . I think the best option is to read the substring for year , month and day and then concate the same to create a valid date ( like 2022-04-21) and then use the date function on the same .

Please do let me if you have any queries.
Thanks
Himanshu

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

Want a reminder to come back and check responses? Here is how to subscribe to a notification

If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
HimanshuSinha 19,637 Reputation points Microsoft Employee Moderator

2022-06-08T16:40:41.283+00:00

Hello @arkiboys ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .In case if you have any resolution please do share that same with the community as it can be helpful to others . Otherwise, will respond back with the more details and we will try to help .
Thanks
Himanshu
arkiboys 9,711 Reputation points

2022-06-08T20:17:34.967+00:00

No longer required.
Thank you