cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

Change datetime format from one to another without changing datatype in databricks sql

richakamat130
New Contributor

Change datetime"2002-01-01T00:00:00.000" to 'MM/dd/yyyy HH:mm:ss' format without changing datatype/ having it in datetime data type

4 REPLIES 4

Mister-Dinky
New Contributor II

I presume then that the "datetime" string you provided is a string column?
If so, try the following code. This will not perform any transformation to timestamps:

from pyspark.sql.functions import lit, split, concat

data = [{"date_string": "2002-01-01T00:00:00.000"}]

data_df = spark.createDataFrame(data) \
    .withColumn(
        "date_string_reformatted", concat(
            lit(split("date_string", "-")[1]), 
            lit("-"),
            lit(split(split("date_string", "-")[2], "T")[0]),
            lit("-"),
            lit(split("date_string", "-")[0]), 
            lit(" "),
            lit(split(split(split("date_string", "-")[2], "T")[1], "\.")[0]),
        )
    )

data_df.display()	

The result:

Screenshot 2024-09-26 143051.png

Hi @Mister-Dinky ,

I think what @richakamat130 would like to obtain is to format datetime according to his needs, but to keep orginal data format (in this case datetime). Unfortunately, this is not possible in spark. If you want to have different display format then you need to have this conversion. 

Ah, the "without changing datatype/ having it in datetime data type" can be interpreted in that way as well. Then I agree with your statement.

filipniziol
Contributor

Hi @Mister-Dinky ,

As @szymon_dybczak if you have a datetime, then you have a datetime.

What you see is just a format of defined in the Databricks UI. Other applications may display it differently depending on the defaults, regional formats etc.

If you want to see datetime displayed differently in Databricks UI, then just navigate to your user settings and change it accordingly to your preferences:

filipniziol_0-1727358771182.png

 

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