Convert Date String to Date type Returning null

[Dp15]

Hi,

I am using Databricks SQL and I am converting a integer field which is of format ('20240719' or 'yyyyMMdd'),
now I am able to convert it to date type using 

to_date(forecastedHorizonPeriodDate,'yyyyMMdd')

I then tried to change the format of this date using the date_format function like 

date_format(to_date(forecastedHorizonPeriodDate,'yyyyMMdd'),'MM/dd/yyyy')

However this function returns a String, 
If I use the to_date function on top of this I am getting null

TO_DATE(date_format(to_date(forecastedHorizonPeriodDate,'yyyyMMdd'),'MM/dd/yyyy'))

Is there a way for me to convert this String type field to Date retaining the format ?

date_format(to_date(forecastedHorizonPeriodDate,'yyyyMMdd'),'MM/dd/yyyy')

Input field and type : '20240719' and Integer
Expected type and format : 07/19/2024 and Date

Is it possible for the date field to be of this format ? 
or does sql table only allow yyyy-mm-dd format for date?

[Slash · Accepted solution]

Hi @Dp15 ,

It's not possible. Spark does not support date types formatted in some other way except for yyyy-MM-dd. If you need another format, you will need to again convert the date type into string type, but with the format which you need.

[Dp15]

Hey @Slash 
Thank you for your answer