- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-11-2022 12:37 PM
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).
- Labels:
-
Code
-
End Date
-
String
-
String Converstion
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-12-2022 07:19 AM
@sri vs if the hours are 0-23 then you'll have to use HH instead of hh:
spark.sql("select date_format(to_timestamp(concat('20221009','010911'),'yyyyMMddHHmmss'),'yyyy-MM-dd hh:mm:ss a') as my_date").display()
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-12-2022 06:57 AM
@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()
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-12-2022 07:15 AM
Hello Matt
concat & to_timstamp function partially worked, values with 24 timestamp format not converted. any other approach i can think .?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-12-2022 07:19 AM
@sri vs if the hours are 0-23 then you'll have to use HH instead of hh:
spark.sql("select date_format(to_timestamp(concat('20221009','010911'),'yyyyMMddHHmmss'),'yyyy-MM-dd hh:mm:ss a') as my_date").display()
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-12-2022 07:24 AM
last expression worked as charm : thank you
![](/skins/images/97567C72181EBE789E1F0FD869E4C89B/responsive_peak/images/icon_anonymous_message.png)
![](/skins/images/97567C72181EBE789E1F0FD869E4C89B/responsive_peak/images/icon_anonymous_message.png)