How to convert string to date in databricks sql

Question

I have a column named opened_dt with a timestamp in string format.

+----------------------------+
|        opened_dt           |
+----------------------------+
| 01/01/2015 21:50:00.000000 |
+----------------------------+

Running

DESCRIBE TABLE 'myTable'

tells me that the column is of type string

+-----------+-----------+---------+
| col_name  | data_type | comment |
+-----------+-----------+---------+
| opened_dt | string    | null    |
+-----------+-----------+---------+

What I want to do:

"Show me all entries starting 01.01.2019 until now".
Which I translate to "select * from 'table' where opened_dt >= 01.01.2019".

Following this I convert opened_dt from to date.

When using:

SELECT cast(opened_dt AS timestamp)

just gives me

+------------+
| opened_dt  |
+------------+
| null       |
+------------+

When using:

SELECT to_date(opened_dt AS timestamp)

just gives me

+---------------------------------+
| to_date('myTable'.`opened_dt`)  |
+---------------------------------+
| null                            |
+---------------------------------+

Other attemps I tried but gives false output:

SELECT * FROM 'myTable' WHERE opened_dt >= '01/01/2019 00:00:00.000000' 

SELECT * FROM 'myTable' WHERE opened_dt IN ('%2019%', '%2020%', '%2021%')

How can I convert the string to date to filter all dates younger than 01.01.2019?

I am looking for something in SQL in DATABRICKS (other answers are in spark).

mck · Accepted Answer · 2021-03-03 10:45:50Z

3

You can cast to timestamp type using to_timestamp and providing a date format string that matches your column's date format.

select *
from myTable
where to_timestamp(opened_dt, 'dd/MM/yyyy HH:mm:ss.SSSSSS') between to_timestamp('2019-01-01') and current_timestamp()

edited Mar 3, 2021 at 10:45

answered Mar 3, 2021 at 9:53

mck

42.7k13 gold badges44 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Wondarar Over a year ago

thanks for your fast reply. I tested it with to_timestamp('2021-03-02') and to_timestamp('2021-02-03') but the output I get is "01/04/2021 16:39:00.000000"

mck Over a year ago

sorry, I didn't know you have entries in the future. See edited answer? @Wondarar

Wondarar Over a year ago

me neither haha. thanks for the edit, works fine :) Meanwhile I tried another attempt "WHERE f11.opened_dt LIKE '%2019%' OR f11.opened_dt LIKE '%2020%' OR f11.opened_dt LIKE '%2021%'" but since I join 10 tables in this query it results in a major performance issue. I guess the problems are the wildcards which are mandatory though, and the amount of joined tables.

Collectives™ on Stack Overflow

How to convert string to date in databricks sql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related