Dynamic M Parameter, SQL

[s_schultes]

Hi,

i was trying to connect the database (MS SQL Server) via direct query and therefore use two dynamic parameters (datetime) for the SQL query.

I have two parameters (Start and End) which are binded with the tables StartSlicer and EndSlicer.

Below is the query for my factdata, where I included the parameters Start and End. The Table is shown and no errors but...

let  
    filter = "
        SELECT
            belegid,
            datum,
            abteilung,
            artikelid,
            menge
        FROM 
            form_belegposition
        WHERE
            datum
        >=
            '" & DateTime.ToText(Start, "dd.mm.yyyy") & "'
        AND
            datum
        <
            '" & DateTime.ToText(Date.AddDays(End,1), "dd.mm.yyyy") & "'",     
            
    Source = Sql.Database(
        "db",
        "slave",
        [
            Query = filter,
            HierarchicalNavigation = true,
            MultiSubnetFailover = true
        ]
    )
in
    Source

...the Picture below shows the error message I get, while creating the visual with my factdata and using the SlicerStart and SlicerEnd tables.

This is my query for the table which I bind to the StartSlicer

let
    filter = "
        SELECT
            d.Datum
        FROM (
            SELECT
                CAST(DATEADD(DAY, n.n, b.MinDate) AS date) AS Datum
            FROM (
                SELECT
                    MIN(TRY_CONVERT(date, datum, 104)) AS MinDate,
                    MAX(TRY_CONVERT(date, datum, 104)) AS MaxDate
                FROM 
                    form_belegkopf
                WHERE 
                    datum IS NOT NULL
                AND 
                    belegtyp IN ('RG','GU','LV','LG')
            ) b
            CROSS JOIN (
                SELECT TOP (40000)
                    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
                FROM sys.all_objects a
                CROSS JOIN sys.all_objects a2
            ) n
            WHERE 
                DATEADD(DAY, n.n, b.MinDate) <= b.MaxDate
            ) d
    ",

    Source = Sql.Database(
        "db",
        "slave",
        [
            Query = filter,
            HierarchicalNavigation = true,
            MultiSubnetFailover = true
        ]
    )
in
    Source

So far all tables are shown right and with direct query, even the binded slicer tables. Maybe someone has a hint or can correct me and my intent. You can answer in english or german.

The Model.

Thanks a lot and kind regards.

Samuel

[v-prasare]

Hi @s_schultes,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[v-prasare]

Hi @s_schultes,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

@Natarajan_M & @AnkitKukreja, Thanks for your prompt response.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[Natarajan_M]

Hi @s_schultes you are able to see the data in the preview pane ?

Thanks

[Natarajan_M]

Hi @s_schultes  , I recreated your issue with some sample data . I think the below line is causing the issue .

& DateTime.ToText(Start, "dd.mm.yyyy") & "'

 Usually mm stands for minutes and MM stands for Months.

I did a quick test in Power Query:

created 2 parameters :

PQuery for testing :

let
    // ── issue SQL 
    BuggySql = "SELECT * FROM form_belegposition WHERE datum >= '"
        & DateTime.ToText(Start, "dd.mm.yyyy") & "' AND datum < '"
        & DateTime.ToText(Date.AddDays(End,1), "dd.mm.yyyy") & "'",

    // ── FIXED SQL 
    FixedSql = "SELECT * FROM form_belegposition WHERE datum >= '"
        & DateTime.ToText(Start, "dd.MM.yyyy") & "' AND datum < '"
        & DateTime.ToText(Date.AddDays(End,1), "dd.MM.yyyy") & "'",

    Debug = #table(
        {"Version", "Generated SQL"},
        {
            {"BUGGY  (dd.mm.yyyy)", BuggySql},
            {"FIXED  (dd.MM.yyyy)", FixedSql}
        }
    )
in
    Debug

OP:

you can see the mm is defaulting to 0 

Change the mm to MM and check 

MMmm issue.pbix

Thanks
If this response was helpful in any way, I’d gladly accept a kudo.
Please mark it as the correct solution. It helps other community members find their way faster

[s_schultes]

@Natarajan_M Hi, I have tried the "MM" but I get the same error in the visuals like before. Could it be, that the parameter is automatically parsed into another datetime format yyyy-MM-dddd hh:mm:ss instead of the format from the database dd.MM.yyyy hh:mm:ss?

[s_schultes]

@AnkitKukreja Hi, thanks unfortunately that doesn't solved the problem. I typed the raw date values into the sql query. Now i get the data table and the visual right. Why don't these parameters have the same effect?

let
    filter = "
        SELECT
            belegid,
            datum,
            abteilung,
            artikelid,
            menge
        FROM 
            form_belegposition
        WHERE
            datum
        >=
            '01.01.2025'
        AND
            datum
        <
           '31.12.2025' 
        ",     
            
    Source = Sql.Database(
        "db",
        "slave",
        [
            Query = filter,
            HierarchicalNavigation = true,
            MultiSubnetFailover = true
        ]
    )
in
    Source

[AnkitKukreja]

Hi! @s_schultes 

The data can be seen because it's a DQ and issue is not recognized at PQ. But when we use the data in visuals it runs the query (DQ) again which looks out if query folding is happening. I don't have a scenario to reproduce, but I gave it a try. Please use below code if that doesn't work go through the below video in case that helps.

https://learn.microsoft.com/en-us/shows/mvp-azure/pass-parameter-to-sql-queries-statement-using-powe...

let
Source = Sql.Database(
"db",
"slave",
[
Query = "
SELECT belegid, datum, abteilung, artikelid, menge
FROM form_belegposition
WHERE datum >= @StartDate
AND datum < DATEADD(DAY, 1, @EndDate)
",
Parameters = [
StartDate = StartDate,
EndDate = EndDate
]
]
)
in
Source