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

Nasif_Azam's avatar
Nasif_Azam
Super User
1 year ago

Hey  davemc69 ,

You are running into this issue because Spark SQL does not support the FORMAT() function, which is valid in T-SQL (like in SSMS) but not in Spark's SQL dialect especially when used in environments like Fabric Spark Notebooks or Lakehouse queries. Instead, you should use date_format(), which is Spark SQL’s equivalent for formatting datetime values.

 

Try the query in Spark SQL:

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;

 

 If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.


Best Regards,
Nasif Azam