Forum Discussion

davemc69's avatar
davemc69
Frequent Visitor
1 year ago
Solved

Notebook sql cell FORMAT DATETIME2 column to yyyy-MMM-dd error

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 FormattedDateField
FROM lakehouse.Schema.TableName
WHERE DateTime2_Column>= CAST('2024-01-01 00:00:00' AS TIMESTAMP)
GROUP BY FORMAT(DateTime2_Column, 'yyyy-MM-dd')
ORDER BY FORMAT(DateTime2_Column, 'yyyy-MM-dd') DESC;
 
I have verified that the column is a datetime2 data type. Even checked for nulls. Everything that I have found via seraching states that this should work. 

 

The cell code throws anerror : “an implementation is missing scala.Predef$.$qmark$qmark$qmark(Predef.scala:288) com.microsoft.fabric.spark.catalog.OnelakeExternalCatalog.functionExists(OnelakeExternalCatalog.scala:386) com.microsoft.fabric.spark.catalog.InstrumentedExternalCatalog.$anonfun$functionExists$1(OnelakeExternalCatalog.scala:607…)

 

I have tried the convert equivalent and also casting the field to a date prior to format. Still receive an error. Ive exhausted my googling capabilities today – everything that I read says that it should work.

Notebook
  • jaineshp's avatar
    jaineshp
    1 year ago

    Hey davemc69,

    You're using T-SQL syntax in a Spark SQL environment - FORMAT doesn't exist in Spark.

    Working Solutions:

    1. Replace FORMAT with DATE_FORMAT:

    SELECT COUNT(1) AS RecordCount,
    DATE_FORMAT(DateTime2_Column, 'yyyy-MM-dd') AS FormattedDateField
    FROM lakehouse.Schema.TableName
    WHERE DateTime2_Column >= '2024-01-01'
    GROUP BY DATE_FORMAT(DateTime2_Column, 'yyyy-MM-dd')
    ORDER BY DATE_FORMAT(DateTime2_Column, 'yyyy-MM-dd') DESC;

     

            2. Simple CAST approach (faster for large tables):

    SELECT COUNT(1) AS RecordCount,
    CAST(DateTime2_Column AS DATE) AS FormattedDateField
    FROM lakehouse.Schema.TableName
    WHERE DateTime2_Column >= '2024-01-01'
    GROUP BY CAST(DateTime2_Column AS DATE)
    ORDER BY CAST(DateTime2_Column AS DATE) DESC;

    Additional Tips:

    • Use DATE_FORMAT for custom formatting
    • Use CAST(column AS DATE) for simple date grouping - it's much faster
    • Test with small dataset first: SELECT DATE_FORMAT(DateTime2_Column, 'yyyy-MM-dd') FROM table LIMIT 5

     

    Fixed? ✓ Mark it • Share it • Help others!

    Best Regards,

    Jainesh Poojara | Power BI Developer

5 Replies

  • BhaveshPatel's avatar
    BhaveshPatel
    Super User
    1 year ago

    Hi davemc69 

     

    I can see that FORMAT is visible in DimDate, even with shortcut mode as well in my version of SSMS. or am I missing something?

     

     

     

     

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Community Support
    1 year ago

    Hi davemc69,

    Have you had a chance to review the solution we shared by jaineshp? If the issue persists, feel free to reply so we can help further.

     

    Thank you.

  • jaineshp's avatar
    jaineshp
    Memorable Member
    1 year ago

    Hey davemc69,

    You're using T-SQL syntax in a Spark SQL environment - FORMAT doesn't exist in Spark.

    Working Solutions:

    1. Replace FORMAT with DATE_FORMAT:

    SELECT COUNT(1) AS RecordCount,
    DATE_FORMAT(DateTime2_Column, 'yyyy-MM-dd') AS FormattedDateField
    FROM lakehouse.Schema.TableName
    WHERE DateTime2_Column >= '2024-01-01'
    GROUP BY DATE_FORMAT(DateTime2_Column, 'yyyy-MM-dd')
    ORDER BY DATE_FORMAT(DateTime2_Column, 'yyyy-MM-dd') DESC;

     

            2. Simple CAST approach (faster for large tables):

    SELECT COUNT(1) AS RecordCount,
    CAST(DateTime2_Column AS DATE) AS FormattedDateField
    FROM lakehouse.Schema.TableName
    WHERE DateTime2_Column >= '2024-01-01'
    GROUP BY CAST(DateTime2_Column AS DATE)
    ORDER BY CAST(DateTime2_Column AS DATE) DESC;

    Additional Tips:

    • Use DATE_FORMAT for custom formatting
    • Use CAST(column AS DATE) for simple date grouping - it's much faster
    • Test with small dataset first: SELECT DATE_FORMAT(DateTime2_Column, 'yyyy-MM-dd') FROM table LIMIT 5

     

    Fixed? ✓ Mark it • Share it • Help others!

    Best Regards,

    Jainesh Poojara | Power BI Developer

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Community Support
    1 year ago

    Hi davemc69,

    Checking in to see if your issue has been resolved. let us know if you still need any assistance.

     

    Thank you.

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Community Support
    1 year ago

    Hi davemc69,

    We haven’t heard back from you in a while regarding your issue. let us know if your issue has been resolved or if you still require support

     

    Thank you.