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

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!

1 answer

  • answered 2021-10-24 23:50 Steve Kass

    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.

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum