issues with datetime2 and datetime functions in fabric warehouse

[lbilali]

DATETIME is not supported in Fabric Warehouse yet GETDATE() and GETUTCDATE() return DATETIME 

Running the code below 

select GETDATE() AS clm1, GETUTCDATE() AS clm2
into dbo.test_table

fails with teh error:

Msg 24574, Level 16, State 1, Line 6
The data type 'datetime' is not supported in this edition of SQL Server.

That's fine to remove a data type but then remove it from everywhere

On the other hand functions SYSDATETIME() and SYSUTCDATETIME() return a DATETIME2(7) but only precission up to 6 is supported as a data type on a table

The code below fails as well

select SYSDATETIME() AS clm1, SYSUTCDATETIME() AS clm2
into dbo.test_table

with error

Msg 24597, Level 16, State 1, Line 3
An integer precision value between 0 and 6 must be specified.

the issues are there for other datetime functions like CURRENT_TIMESTAMP as well 

I understand that those cases can be fixed by converting to correct data type/precission
but this should not be the case in the first place

 

[lbilali]

Link to Fabric Ideas T-SQL datetime2 and datetime functions should retu... - Microsoft Fabric Community

[v-veshwara-msft]

Hi @lbilali ,
Thank you for sharing the Ideas link. This will help others with similar requirements upvote it, increasing the chances of consideration by the product team.

[v-veshwara-msft]

Hi @lbilali ,
Thanks for reaching out to Microsoft Fabric Community.

In Fabric Data Warehouse, the supported T SQL surface area is a subset of the SQL Server engine. The DATETIME data type is not supported, and DATETIME2 is the recommended type for storing date and time values, with supported precision up to 6.

Data types in Fabric Data Warehouse

datetime2 (Transact-SQL) - SQL Server | Microsoft Learn

Fabric Warehouse uses a reduced T SQL type system because the storage engine is optimized for distributed analytics and Parquet based storage, where types like DATETIME2 align better with the underlying architecture.

Functions such as GETDATE(), GETUTCDATE(), and CURRENT_TIMESTAMP still return DATETIME due to their historical SQL Server definitions. Since DATETIME cannot be used as a table column type in Fabric Warehouse, using these functions directly in operations like SELECT INTO results in the error you observed. Similarly, SYSDATETIME() and SYSUTCDATETIME() return DATETIME2(7), while Fabric Warehouse supports precision only up to 6.

In these cases, the value needs to be explicitly cast to a supported type and precision such as DATETIME2(6) before writing it to a table.

If you think this behaviour should be improved, you may consider sharing the suggestion in the Fabric Ideas forum. This helps bring it to the attention of the product team for review.

Hope this helps. Please reach out for further assistance.
Thank you.

[lbilali]

Thanks for the link to Fabric Ideas forum 

I think this belogs there if it is not yet.

[v-veshwara-msft]

Hi @lbilali ,

Thank you for the update.

If you have submitted it in the Fabric Ideas forum, please feel free to share the link here as well. It can help others in the community track the suggestion and add their votes or feedback.

Please reach out for further assistance.
Thank you.