greatest() in sql

Question

hi
I am on
Microsoft SQL Server 2017 (RTM-GDR) (KB4583456) - 14.0.2037.2 (X64) Nov 2 2020 19:19:59 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19042: )

when I try
select GREATEST(getdate(),cast('31-mar-2020' as date)) x
I get below error

Msg 195, Level 15, State 10, Line 67
'GREATEST' is not a recognized built-in function name.

how can I enable above in my version?
please help

Answer 1

Documentation says that GREATEST applies to Azure databases. Consider the alternatives:

select case when getdate() > '31-mar-2020' then getdate() else '31-mar-2020' end x

select iif(getdate() > '31-mar-2020', getdate(), '31-mar-2020') x

Answer 2

+1 @Viorel

This is great solution when you need to use GREATEST between two expressions

With that said, try this on 100 expressions will be really poor performance and complex to write.

Another solution which might fit better in such cases is implement GREATEST idea using simple MAX function

For example except of using:

SELECT GREATEST ( '6.62', 3.1415, N'7' ) AS GreatestVal;   
GO   

In SQL Server we can use:

SELECT MAX(GreatestVal) FROM (VALUES('6.62'), (3.1415), (N'7') )T(GreatestVal)   
GO  

By the way, the power of developing a built-in function like GREATEST is that it can implement complex algorithm to find the greatest expressions or even multiple algorithms and selecting one which is best on the fly in theory.

Answer 3

thankssssssssssss