Passing multiple dates and retrieve the most recent one

I had about 7 dates in a table that I needed to be able to compare between and retrieve only the most recent date of them.

 

Our SQL Grandmaster, Mr. Jim Wang was kind enough to spend a bit of time to create an smart SQL function to compare between two passed dates and output the recent one of them.

 

The function looks something like:

CREATE Function [dbo].[fn_MAX](@t1 DATETIME, @t2 DATETIME)

Returns DATETIME

As

Begin

 Declare @t DATETIME  

  IF @t1 = NULL     

   SELECT @t = @t2

  ELSE IF @t2 = NULL     

   SELECT @t = @t1 

  ELSE IF @t1 > @t2      

   SELECT @t = @t1

  ELSE     

   SELECT @t = @t2  

  Return @t

END  

And to use the function in an external query, it was just a matter of calling the function and passing the dates to compare and when wanting to compare more dates, it was all about managing the brackets and passing either a proper date or an empty string as the function would not work with NULL values.

 

So to compare between seven dates, I wrote the following within the SQL query. Notice the addition of ISNULL function to replace the NULL values with empty string as I was getting undesired result.

 [dbo].[fn_MAX](

      [dbo].[fn_MAX](

            [dbo].[fn_MAX](

                  [dbo].[fn_MAX](

                        [dbo].[fn_MAX](

                              [dbo].[fn_MAX](

                                          ISNULL(uf_firstDate, ''),

                                          ISNULL(uf_secondDate, '')),

                                          ISNULL(uf_thirdDate, '')),

                                          ISNULL(uf_fourthDate, '')),

                                          ISNULL(uf_fifthDate, '')),

                                          ISNULL(uf_sixthDate, '')),

                                          ISNULL(uf_seventhDate, ''))

                        AS RecentDate

                         

Quite simple but very useful!