topic Re: How to create a widget in SQL with variables? in Get Started Discussions

How to create a widget in SQL with variables?

zc — Sat, 26 Jul 2025 05:19:15 GMT

I want to create a widget in SQL and use it in R later. Below is my code

%sql

declare or replace date1 date = "2025-01-31";

declare or replace date2 date ;

set var date2=add_months(date1,5);

What's the correct syntax of using date2 to create a widget? I have tried

CREATE WIDGET TEXT date_end DEFAULT date2;

CREATE WIDGET TEXT date_end DEFAULT $date2;

CREATE WIDGET TEXT date_end DEFAULT :date2;

and none worked.

If there are other ways to let R access a variable in SQL, I'd love to learn that too.

Thank you,

Re: How to create a widget in SQL with variables?

Khaja_Zaffer — Mon, 28 Jul 2025 08:29:07 GMT

Hello @zc

This should work.

https://docs.databricks.com/aws/en/notebooks/widgets

Re: How to create a widget in SQL with variables?

Khaja_Zaffer — Mon, 28 Jul 2025 08:32:59 GMT

when using SQL to create a widget and set its default value to a previously declared variable, you should use a colon (:) prefix to reference the variable.

The correct syntax for your scenario is:

SQL

CREATE WIDGET TEXT date_end DEFAULT :date2;

Re: How to create a widget in SQL with variables?

szymon_dybczak — Mon, 28 Jul 2025 08:56:01 GMT

Hi @zc ,

Unfortunately, I think in case of sql widgets default value needs to be string literals. So above approach won't work.

Regarding your second question about accessing variables decalared in SQL in R cell, you cannot do such a thing. Here's an excerpt from documentation:

"When you invoke a language magic command, the command is dispatched to the REPL in the execution context for the notebook. Variables defined in one language (and hence in the REPL for that language) are not available in the REPL of another language. REPLs can share state only through external resources such as files in DBFS or objects in object storage."

Develop code in Databricks notebooks | Databricks Documentation

Re: How to create a widget in SQL with variables?

zc — Mon, 28 Jul 2025 15:10:43 GMT

Thank you for responding, Khaja. But the code you posted won't work as I described in my question.

Re: How to create a widget in SQL with variables?

zc — Mon, 28 Jul 2025 15:14:44 GMT

Hi @szymon_dybczak

Thank you for the information. If I understand correctly, you meant one cannot use a SQL variable for a SQL widget, but only hard coded strings?

Re: How to create a widget in SQL with variables?

szymon_dybczak — Mon, 28 Jul 2025 16:06:10 GMT

Hi @zc ,

Exactly, this is what I meant. What is funny though, that in python cell you can define variable and then later use it as a widget's default value. So there's a bit of inconsistency here between different types of cells.

Following works in python cell:

from datetime import datetime from dateutil.relativedelta import relativedelta date1_str = "2025-01-31" date2_str = None date1_obj = datetime.strptime(date1_str, "%Y-%m-%d").date() date2_obj = date1_obj + relativedelta(months=5) date2_str = date2_obj.strftime("%Y-%m-%d") print(f"date1: {date1_str}") # 2025-01-31 print(f"date2: {date2_str}") # 2025-06-30 dbutils.widgets.text("date1", date1_str) dbutils.widgets.text("date2", date2_str)

Re: How to create a widget in SQL with variables?

zc — Mon, 28 Jul 2025 16:55:58 GMT

Hi @szymon_dybczak

Thank you for the details. I figured that part out using R as well. Turns out it's easy to share variables from Python/R to SQL, but not the other way around.

Re: How to create a widget in SQL with variables?

Khaja_Zaffer — Tue, 29 Jul 2025 12:30:20 GMT

Hello @szymon_dybczak
Thanks for the information, I did a repo of this, indeed I was using all purpose cluster and found that while SQL commands doesnt help in creating widgets if used as date format. but it worked when I converted date into string. while microsoft didnt explicitly mentioned this, I wonder hwo did you figured it out?

Re: How to create a widget in SQL with variables?

Khaja_Zaffer — Tue, 29 Jul 2025 12:30:49 GMT

2/2 and why it doesnt work any idea?

Re: How to create a widget in SQL with variables?

szymon_dybczak — Tue, 29 Jul 2025 13:29:33 GMT

Hi @Khaja_Zaffer ,

Sure, I just checked what are expected data types for arguments using dbutils.widgets.help(). As you can see, for defaultValue they require String data type.

Re: How to create a widget in SQL with variables?

Khaja_Zaffer — Wed, 30 Jul 2025 09:02:34 GMT

Hello @szymon_dybczak

Indeed it is. Sorry that I could resolve the issue at first instance. but yeah, it accepts strings no matter which widget it is.