Forum Discussion

davemc69's avatar
davemc69
Frequent Visitor
1 year ago
Solved

FORMAT DATETIME Field spark sql notebook cell

  Attempting to use code that works in SSMS summarizing table by date and formatting the date to 'yyyy-MMM-dd'. SELECT COUNT(1) AS RecordCount, FORMAT(DateTime2_Column, 'yyyy-MM-dd') AS FormattedD...
one lake
  • shashiPaul1570_'s avatar
    shashiPaul1570_
    1 year ago

    Hi davemc69, 
    Thank you for sharing this problem. 
    You're encountering this issue because the FORMAT() function you're using is valid in T-SQL (SSMS) but not supported in Spark SQL, which is what Fabric notebooks are based on.

     

    Why the error occurs

    Spark SQL does not support the FORMAT function. Instead, it uses a different function called date_format for formatting datetime values. That’s why you're seeing an error like

    scala.Predef$.$qmark$qmark$qmark ... functionExists ...

    Which means, Spark saying "I don't recognize this function."

    How to fix it

    Replace FORMAT with date_format in your SQL cell.

    As per your existing query

    SELECT 
  COUNT(1) AS RecordCount, 
  date_format(DateTime2_Column, 'yyyy-MMM-dd') AS FormattedDateField
FROM 
  lakehouse.Schema.TableName
WHERE 
  DateTime2_Column >= TIMESTAMP('2024-01-01 00:00:00')
GROUP BY 
  date_format(DateTime2_Column, 'yyyy-MMM-dd')
ORDER BY 
  FormattedDateField DESC

    This version is compatible with Fabric notebooks and will produce the same result you're expecting.

     

    For your reference, you can also follow this link

    https://spark.apache.org/docs/latest/sql-ref-functions.html#date_format

     

    Let me know if this resolves your issue — and if helpful, please mark this as the solution to assist others facing the same challenge.
    Thanks!
    – Shashi Paul | Fabric Community Member

v-ssriganesh's avatar
v-ssriganesh
Community Support
1 year ago

Hello davemc69,
Thank you for reaching out to the Microsoft Fabric Community Forum and thanks Nasif_Azam & shashiPaul1570_ for sharing your valuable insights.

I have reproduced your scenario using Microsoft Fabric Notebook by creating a sample table with datetime values. As you correctly observed, the FORMAT() function works in T-SQL (SQL Server Management Studio), but it is not supported in Spark SQL, which is why you're encountering the "missing implementation" error.

To achieve the same formatted output in Spark SQL, the equivalent function is date_format().

Working Spark SQL Query:

SELECT

  COUNT(1) AS RecordCount,

  date_format(DateTime2_Column, 'yyyy-MMM-dd') AS FormattedDateField

FROM SampleTable

WHERE DateTime2_Column >= TIMESTAMP('2024-01-01 00:00:00')

GROUP BY date_format(DateTime2_Column, 'yyyy-MMM-dd')

ORDER BY FormattedDateField DESC;

 

Output (Sample):

 

This query successfully groups and formats the datetime column as required.

Best regards,
Ganesh Singamshetty.