-1

I have a column which has a date in the format of MM/dd/YYYY HH:MM:SS AM/PM. But currently the format is in string type. While loading this data into Oracle table, it is giving me type conversion error from String to Date type.

I tried to_date() and date_format() functions in pyspark but to_date() is truncating my value to only date MM/dd/YYYY and date_format() is keeping it in string only.

Is there any way i change the datatype of my column?

Syed Shahzer
  • 310
  • 4
  • 14
Samyak Jain
  • 155
  • 1
  • 2
  • 8
  • Does this answer your question? [Convert pyspark string to date format](https://stackoverflow.com/questions/38080748/convert-pyspark-string-to-date-format) – blackbishop Jan 07 '22 at 12:32

1 Answers1

0

You can use to_timestamp function to convert the string to timestamp format.

from pyspark.sql.functions import *
df = spark.createDataFrame(data = [('01/07/2022 10:12:00 AM',),], schema = ['ts_as_str'])

#to_timestamp function converts to specified format of timestamp, as in 2nd argument.
df_1 = df.select(to_timestamp(df.ts_as_str, 'MM/dd/yyyy HH:mm:ss a').alias('ts_as_ts'))

df_1.printSchema()
display(df_1)

Converted column as timestamp

Load the converted dataframe to oracle. Oracle or another RDBMS has its own way of handling date. So, the format you see in oracle could be different from this one.

Jim Todd
  • 1,488
  • 1
  • 11
  • 15