format_datetime inside dynamic data type kusto

Question

I have a function that takes startdate and endDate as inputs. From within the function I perform the below operations-

use format_datetime to convert the input date into a specified format
use sql_request plugin and pass these formatted dates as sql parameters.

let start_time = todynamic(format_datetime(startTs, 'yyyy-MM-dd'));
let end_time = todynamic(format_datetime(endTs, 'yyyy-MM-dd'));
let query = 'select * from [db].[Table] where date > @param0 and date < @param1';
let result = evaluate sql_request(auth, query, dynamic({'param0': start_time, 'param1': end_time}))

However, on the editor I get syntax error on start_time as it says "expected '}' " and also on sql_request saying that sql_request expects 4 arguments.

How do I pass formatted datetime as part of dynamic data type? Thanks!

user_6336479 · Answer

Unfortunately, at this point plugins support only constant arguments.
This might change in the future.

Some additional remarks:

Parameters names must start with @ (both in the SQL query and the SQL parameters definition)
Parameters might have a datetime type. No need to convert them to string type.
dynamic() can only be used with constants, E.g. dynamic({"x":1,"y":2}).

For non constants we should use pack(), bag_pack() or pack_dictionary() (all aliases to each other), E.g.
```
let x_val = 1;  
let y_val = 2;  
let my_dict = pack_dictionary("x", x_val, "y", y_val);
print my_dict 
```
print_0

{"x":1,"y":2}

Demo:

let connection_string   = h'Server=tcp:dumarkov.database.windows.net,1433;Initial Catalog=mydb;Encrypt=True;Authentication="Active Directory Integrated";';
let sql_query           = "select * from sys.tables where create_date >= @mydatetime";
let sql_parameters      = dynamic({"@mydatetime":datetime("2022-06-21 00:00:00")});
evaluate sql_request(connection_string, sql_query, sql_parameters)

name	object_id	principal_id	schema_id	parent_object_id	type	type_desc	create_date	modify_date	is_ms_shipped	is_published	is_schema_published	lob_data_space_id	filestream_data_space_id	max_column_id_used	lock_on_bulk_load	uses_ansi_nulls	is_replicated	has_replication_filter	is_merge_published	is_sync_tran_subscribed	has_unchecked_assembly_data	text_in_row_limit	large_value_types_out_of_row	is_tracked_by_cdc	lock_escalation	lock_escalation_desc	is_filetable	is_memory_optimized	durability	durability_desc	temporal_type	temporal_type_desc	history_table_id	is_remote_data_archive_enabled	is_external	history_retention_period	history_retention_period_unit	history_retention_period_unit_desc	is_node	is_edge	data_retention_period	data_retention_period_unit	data_retention_period_unit_desc	ledger_type	ledger_type_desc	ledger_view_id	is_dropped_ledger_table
hello	1605580758		1	0	U	USER_TABLE	2022-06-23T14:22:23.723Z	2022-06-23T14:22:23.723Z	false	false	false	0		1	false	true	false	false	false	false	false	0	false	false	0	TABLE	false	false	0	SCHEMA_AND_DATA	0	NON_TEMPORAL_TABLE		false	false				false	false	-1	-1	INFINITE	0	NON_LEDGER_TABLE		false
world	1653580929		1	0	U	USER_TABLE	2022-06-25T09:21:28.36Z	2022-06-25T09:21:28.36Z	false	false	false	0		1	false	true	false	false	false	false	false	0	false	false	0	TABLE	false	false	0	SCHEMA_AND_DATA	0	NON_TEMPORAL_TABLE		false	false				false	false	-1	-1	INFINITE	0	NON_LEDGER_TABLE		false

format_datetime inside dynamic data type kusto

1 Answers

Demo: