How to convert YYYYMMDD string to date in databricks SQL end point?

Question

How to convert YYYYMMDD string to date in databricks SQL end point?

Input values:- 1. 20230804

           2. 20230805

output values :- 1. 2023-08-04

             2. 2023-08-05

Answer 1

SELECT
  TO_DATE(your_column_name, 'yyyyMMdd') as formatted_date
FROM
  your_table_name;

Answer 2

Try this

SELECT DATE_FORMAT(CAST('2023-08-04' AS DATE), 'dd MM yyyy') AS formatted_date;

Result will be 2023-08-04

If you want month in letters abbreviated to three characters

SELECT DATE_FORMAT(TO_DATE('20230804', 'yyyyMMdd'), 'yyyy MMM dd') AS formatted_date;

Result will be 2023-Aug-04 If you want Full Name of the month

SELECT DATE_FORMAT(TO_DATE('20230804', 'yyyyMMdd'), 'yyyy MMMM dd') AS formatted_date;

Result will be 2023-August-04