SQL: The datepart minute is not supported by date function dateadd for data type date

I was asked about this error message by a client just last week. They had what they thought was straightforward code, and yet were getting the error above.

What they were trying to achieve was to calculate the time as 10 minutes past midnight on the same day. So, they’d used SYSDATETIME() to get the current time (it could have been GETDATE() but SYSDATETIME() is the current version that returns a datetime2 data type).

They’d then CAST the value to date to remove the time portion. So far so good:

But then they tried to pass that value into the DATEADD function and while it will accept a date parameter, it won’t let you add minutes to it. I actually think that isn’t a sensible restriction ie: you should be able to pass a date to a function that requires a datetime. But alas you can’t do that here.

What they needed to do was to then cast the value to a datetime (or datetime) and then add the 10 minutes:

And all works fine.

2018-05-28