-1

I have a timestamp in the below format. How to get date in yyyy-MM-dd format from it?

+-------------------------+
|15APR2021:03:53:54.458387|
|15APR2021:03:53:53.830339|
|15APR2021:03:53:54.297856|
+-------------------------+

I have tried using the below but is getting null.

scala> spark.sql("select to_date('15APR2021:03:53:54.297856')").show()
+------------------------------------+
|to_date('15APR2021:03:53:54.297856')|
+------------------------------------+
|                                null|
+------------------------------------+
Manoj Kumar G
  • 492
  • 8
  • 18

2 Answers2

2

This should work for you. If time is 24Hr format then change hh to HH.

spark.sql("select to_date('15APR2021:03:53:54.458387', 'ddMMMyyyy:hh:mm:ss')").show()
Mohana B C
  • 5,021
  • 1
  • 9
  • 28
0

Just need to use the proper date mask which it seems to be:

ddMMMyyyy:HH:mm:ss.SSSSSS

Here is an example assuming timestamp is in a column called ts

df.withColumn("date", to_date($"ts", "ddMMMyyyy:HH:mm:ss.SSSSSS")).show(false)

+-------------------------+----------+
|ts                       |date      |
+-------------------------+----------+
|15APR2021:03:53:54.458387|2021-04-15|
|15APR2021:03:53:53.830339|2021-04-15|
|15APR2021:03:53:54.297856|2021-04-15|
+-------------------------+----------+

Or using syntax from your example:

spark.sql("select to_date('15APR2021:03:53:54.458387', 'ddMMMyyyy:hh:mm:ss.SSSSSS')").show()

+-----------------------------------------------------------------+
|to_date('15APR2021:03:53:54.458387', 'ddMMMyyyy:hh:mm:ss.SSSSSS')|
+-----------------------------------------------------------------+
|                                                       2021-04-15|
+-----------------------------------------------------------------+
SCouto
  • 7,808
  • 5
  • 32
  • 49
  • 1
    thanks for the help. However, it is still giving me null. I am using spark version 2.3. May be that is why it is not working for me. – Manoj Kumar G Aug 09 '21 at 11:53