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

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