topic Notebook sql cell FORMAT DATETIME2 column to yyyy-MMM-dd error in Data Engineering

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

davemc69 — Fri, 01 Aug 2025 20:41:37 GMT

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.

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

jaineshp — Sat, 02 Aug 2025 04:55:09 GMT

Hey davemc69,

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

Working Solutions:

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

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

BhaveshPatel — Sun, 03 Aug 2025 09:15:04 GMT

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?

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

v-saisrao-msft — Mon, 04 Aug 2025 06:58:44 GMT

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.

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

v-saisrao-msft — Thu, 07 Aug 2025 07:05:12 GMT

Hi davemc69,

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

Thank you.

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

v-saisrao-msft — Mon, 11 Aug 2025 03:12:20 GMT

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.