SQL Declare Variable equivalent in databricks

[elgeo]

Hello. What would be the equivalent of the below in databricks?

DECLARE @LastChangeDate as date

SET @LastChangeDate = GetDate()

I already tried the below and worked. However I need to know how set a sql variable dynamically

SET da.dbname = test;

SELECT "${da.dbname}" AS db_name

Thank you in advance.

[elgeo]

Hello. Is the above something that can be done in databricks sql?

[Soma]

@ELENI GEORGOUSI​ 

set LastChangeDate =current_date();

or

in your sql query

select current_date() as date

[elgeo]

Hello @somanath Sankaran​. Thank you for your reply. Neither of the above worked. Please find attached the related screenshots:

 

Any other ideas please? Thank you in advance

[Soma]

[elgeo]

Hello @somanath Sankaran​. I need an equivalent to Declare variable. In the above example you attached, I cannot refer to this variable as for example ${dt} throughout the notebook.

[elgeo]

A workaround is the below:

%python

result = spark.sql("""select date_format(current_date(),"yyyy-MM-dd") as dt""")

spark.conf.set('da.businessbr',result.collect()[0][0])

select '${da.businessbr}'

[DeepakMakwana74 · Accepted solution]

Hi @ELENI GEORGOUSI​ 

In databricks sql it will works in the following manner

​SET LastChangeDate =current_date()

​Select ${hiveconf:LastChangeDate}

[JohnLerch]

Hello - thanks for this suggestion.  However, I am getting this error in databricks: 

"Error running query: [_LEGACY_ERROR_TEMP_DBR_0222] org.apache.spark.sql.AnalysisException: Configuration LastChangeDate is not available."

I've tried many different suggestions for this and can't seem to find a way to do this simple thing.

[srinitechworld]

hi try to to control the variables