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!

Get data modified yesterday and/or during the weekend

I had a requirement to extract the data modified during yesterday’s date and for Mondays, the client wanted to be able to extract the data modified on the last Friday and during the weekend.

It should be noted that the table contained a field called “ModifiedDate” and the following was added to the SQL query to calculate the start and end dates and check whether today “the extraction date” is Monday, if so then return the data for the last three days rather than just the previous day and if not Monday then returns only the data modified during the previous date.

DECLARE @startDateCount AS DATETIME
DECLARE @endDateCount AS DATETIME
DECLARE @today AS DATETIME

SET @today = CONVERT(VARCHAR(10), GETDATE(), 101)
SET @endDateCount = @today

IF (DATENAME(dw, GETDATE()) = 'Monday')
SET @startDateCount = @today - 3
ELSE
SET @startDateCount = @today - 1

-- TO TEST
-- SELECT @startDateCount AS [START DATE], @endDateCount AS [END DATE], @today AS TODAY
--SELECT * FROM TABLENAME--WHERE FIELDNAME BETWEEN @startDateCount AND @endDateCount OR

I could have used the @today alone but to make it easier to read, I preferred to create an extra @endDateCount date. Also, the above contained a few useful Transact-SQL functions such as the CONVERT which converts the DATE value into VARCHAR in a format of your choice. The format chosen above is MM/DD/YYYY, this is specified by the third parameter of the function which is 101 as shown above. For a complete list of these formats, check out the following blog: http://www.sql-server-helper.com/tips/date-formats.aspx

Also, the DATENAME function which returns the character string representing the specified datepart of the specified date. For further info on this function, check the MSDN link below: http://msdn2.microsoft.com/en-us/library/ms174395.aspx

The link to my original post: Get data modified yesterday and/or during the weekend