topic Re: String converstion to datetimestamp format in Data Engineering

String converstion to datetimestamp format

refint650 — Tue, 11 Oct 2022 19:37:46 GMT

Hello

i'm converting hana sql code in databricks. we have 4 columns all in string format, start date, start time, end date, endtime..

1) what expression i can use to convert values of startdate & start time from string format to datetimeformat with AM/PM .? so later i can break final value in to two columns.

expected value:

Startdate ,, Starttime

2022-10-09 01-09-11 am

2022-10-09 -08-20-16 am (3rd row).

Re: String converstion to datetimestamp format

Matt101122 — Wed, 12 Oct 2022 13:57:12 GMT

@sri vs maybe concatenate the two strings and use to_timestamp with the format specified?

https://docs.databricks.com/spark/latest/spark-sql/language-manual/functions/to_timestamp.html

https://docs.databricks.com/spark/latest/spark-sql/language-manual/functions/concat.html

creating timestamp:

spark.sql("select to_timestamp(concat('20221009','010911'),'yyyyMMddhhmmss') as my_date").display()

formatted with AM /PM:

spark.sql("select date_format(to_timestamp(concat('20221009','010911'),'yyyyMMddhhmmss'),'yyyy-MM-dd hh:mm:ss a') as my_date").display()

Re: String converstion to datetimestamp format

refint650 — Wed, 12 Oct 2022 14:15:07 GMT

Hello Matt

concat & to_timstamp function partially worked, values with 24 timestamp format not converted. any other approach i can think .?

Re: String converstion to datetimestamp format

Matt101122 — Wed, 12 Oct 2022 14:19:13 GMT

@sri vs if the hours are 0-23 then you'll have to use HH instead of hh:

https://docs.databricks.com/spark/latest/spark-sql/language-manual/sql-ref-datetime-pattern.html#pattern-table

spark.sql("select date_format(to_timestamp(concat('20221009','010911'),'yyyyMMddHHmmss'),'yyyy-MM-dd hh:mm:ss a') as my_date").display()

Re: String converstion to datetimestamp format

refint650 — Wed, 12 Oct 2022 14:24:44 GMT

last expression worked as charm : thank you