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

[davemc69]

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.

[jaineshp · Accepted solution]

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

[BhaveshPatel]

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]

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.

[v-saisrao-msft]

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]

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.