Issue with Date Casting in Fabric SQL UDF: read_from_sql_db Fails with InternalServerError

[abhidotnet]

I'm working in a Microsoft Fabric workspace and using a user-defined function (UDF) to query a Fabric SQL database and return results as a pandas DataFrame. The function is defined as follows:

import pandas as pd
import fabric.functions as fn

udf = fn.UserDataFunctions()

@udf.connection(argName="sqlDB", alias="RAMSES2010dbMIRROR")
@udf.function()
def read_from_sql_db(sqlDB: fn.FabricSqlConnection, query: str) -> pd.DataFrame:
    connection = sqlDB.connect()
    df = pd.read_sql(query, connection)
    return df

 This works fine for most queries where I am getting string and numbers.
However, when I attempt to cast date fields using either CAST() or TRY_CAST() in my SQL query, the function fails with the following error:

{
  "functionName": "read_from_sql_db",
  "invocationId": "00000000-0000-0000-0000-000000000000",
  "status": "Failed",
  "errors": [
    {
      "errorCode": "WorkloadException",
      "subErrorCode": "InternalServerError",
      "message": "User data function: 'read_from_sql_db' invocation failed."
    }
  ]
}

I have tried two methods to cast the date.

SELECT CAST([Expiration Date] AS DATE) AS [Expiration Date] FROM [dbo].[vw_LeaseActivationReport]

SELECT TRY_CAST([Expiration Date] AS DATE) AS [Expiration Date] FROM [dbo].[vw_LeaseActivationReport]

• Running the same query directly in the SQL endpoint works fine and returns valid dates.

• When using the UDF, the date columns are either blank or replaced with {} in the resulting DataFrame.
  

Is this a known issue with the Fabric Platform?
How can I get this to work correctly?

Environment:

• Workspace: Microsoft Fabric

• SQL Source: Fabric SQL Mirror Database ()

• UDF Framework: fabric.functions.UserDataFunctions

• Python Runtime: Fabric Notebook

• Connector: sqlDB.connect() via FabricSqlConnection

[abhidotnet]

This worked for me.

• In your SQL query, convert the date to a string format (e.g., YYYY-MM-DD) using CONVERT(VARCHAR, [Expiration Date], 23), then parse it to a datetime in pandas.

[v-ssriganesh · Accepted solution]

Hello @abhidotnet,
Thank you for reaching out to the Microsoft Fabric Community Forum. The issue with casting dates in your UDF likely stems from how the Fabric SQL connector handles DATE types when converting to a pandas DataFrame. Since the query works in the SQL endpoint, try these workarounds:

• Remove CAST or TRY_CAST from your query and convert the [Expiration Date] column to a datetime in pandas using a post-processing step.
• In your SQL query, convert the date to a string format (e.g., YYYY-MM-DD) using CONVERT(VARCHAR, [Expiration Date], 23), then parse it to a datetime in pandas.
• Verify the [Expiration Date] column in the SQL endpoint for NULL or invalid values that might cause issues.

If you have any further questions, please don't hesitate to contact us through the community. We are happy to assist you.

Best Regards,
Ganesh Singamshetty.