greatest() in sql

Hemant Karnik 121 Reputation points

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,574 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,454 questions
{count} votes

Accepted answer
  1. Viorel 106.4K Reputation points

    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
    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Ronen Ariely 15,066 Reputation points

    +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;   

    In SQL Server we can use:

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

    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.

    3 people found this answer helpful.

  2. Hemant Karnik 121 Reputation points


    0 comments No comments