Convert Date String to Date type Returning null

Dp15 — Fri, 19 Jul 2024 23:01:21 GMT

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?

Re: Convert Date String to Date type Returning null

szymon_dybczak — Sat, 20 Jul 2024 02:24:17 GMT

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.

Re: Convert Date String to Date type Returning null

Dp15 — Mon, 22 Jul 2024 13:25:20 GMT

Hey @szymon_dybczak
Thank you for your answer

topic Convert Date String to Date type Returning null in Get Started Discussions

Convert Date String to Date type Returning null

Re: Convert Date String to Date type Returning null

Re: Convert Date String to Date type Returning null