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
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.
yes @arkiboys.
Please let me know if you find above reply useful. If yes, please 'Accept the answer' for the above reply. This will help other community members facing similar query to refer to this solution. Thanks.
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
10 people are following this question.
