cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Community Platform Discussions
Connect with fellow community members to discuss general topics related to the Databricks platform, industry trends, and best practices. Share experiences, ask questions, and foster collaboration within the community.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Convert Date String to Date type Returning null

Dp15
Contributor

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?

1 ACCEPTED SOLUTION

Accepted Solutions

szymon_dybczak
Contributor III

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.

 

View solution in original post

2 REPLIES 2

szymon_dybczak
Contributor III

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
Contributor

Hey @szymon_dybczak 
Thank you for your answer

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group