(Databricks SQL) Date stored as STRING (yyyyMMDD) cannot convert it into DATE (yyyy-MM-DD)

Question

I've spent last 3 hours googling the issue but nothing seems to work in Databricks SQL version specifically. I have to use a database, where someone decided it's best to store date as a STRING, there's no way around it short-term.

Current date format (STRING) is following: yyyyMMDD

What I want it to be is (DATE): yyyy-MM-DD

I tried CAST, I tried CONVERT, I tried 5 different solutions from stack and other website but the code seems to be written for SQL Dev where there are more functions available and I am forced to work with Databricks. I'd be grateful for any help!

tx!

Can you show us your examples? And why are you using so many tags if the problem doesn't belong to that tags? — Leandro Bardelli, Oct 24 '21 at 16:50
Please provide enough code so others can better understand or reproduce the problem. — Community, Oct 24 '21 at 17:15

score 0 · Answer 1 · answered Oct 24 '21 at 23:50

I know nothing about Databricks, but after some googling, it looks like there is a MAKE_DATE function that can help you. You can extract the year, month, and day values from your YYYYMMDD string. It might look something like this, if your date string is called ds:

MAKE_DATE(
  SUBSTRING(ds FROM 1 FOR 4),
  SUBSTRING(ds FROM 5 FOR 2),
  SUBSTRING(ds FROM 7 FOR 2)
)

If you want the code to be even more transparent, you can cast the substrings to integers yourself instead of relying on implicit conversion for the arguments to MAKE_DATE.

(Databricks SQL) Date stored as STRING (yyyyMMDD) cannot convert it into DATE (yyyy-MM-DD)

1 Answers1