String converstion to datetimestamp format

[refint650]

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).

[Matt101122]

@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()

[refint650]

Hello Matt

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

 

[Matt101122 · Accepted solution]

@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#pat...

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

[refint650]

last expression worked as charm : thank you