dateformat

Question

hello,
What is the equivalent of this T_sql in sql server but in databricks sql?
Thank you

CAST(
      DATEFROMPARTS(
          LEFT(table1.id,2)+2000,
          SUBSTRING(CAST(table1.id AS NVARCHAR(9)),3,2),
          SUBSTRING(CAST(table1.id AS NVARCHAR(9)),5,2)
          )
      AS DATE) AS DATA_REF

Answer 1

Hi @arkiboys ,

Thanks for using Microsoft Q&A !!
If I understand you correctly, you have dates stored like below in a Databricks table and you want to construct a Date value in a Notebook using Databricks SQL using this stored data.

You need to use the below SQL code in your Notebook to get the desired results -

SELECT make_date(LEFT(Date,2)+2000,substr(Date,3,2),substr(Date,5,2)) as Date FROM Date_Table

Result:

You can refer to Databricks documentation to get more details on the above used functions.

Please let me know if you have any questions.

Thanks
Saurabh

----------

Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.

Answer 2

Alternatively, turn the string into a date as below:

%sql  
-- here we add "20" to the start of the original date string and turn it into a date type  
SELECT to_date("20" || "121004", 'yyyyMMdd') AS dateType