TSQL UDF to return useful dates

A User Defined Function to return useful dates

I had to come up with some calculations for working out the starting and end weekday for a given date earlier and I wrote this UDF for SQL 2000, 2005, 2008.

It returns a number of useful values including dates and strings (which is why the return value is a varchar).

If you want to know the last working day for the current month, last month or the last weekday for a month then this function will help.

You can pass in the current date e.g GETDATE() or pass in your own datetime value.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

=============================================================================
-- Author: Rob Reid
-- Create date: 05-JUL-2011
-- Description: Returns useful dates for calculations and formatting
/*


-- example usage

DECLARE @dte datetime
SELECT  @dte = GETDATE() --OR pass in a literal date e.g '2011-Jun-03 03:54:00'

SELECT dbo.udf_GET_DATE_OF('FIRST DAY OF LAST MONTH',@dte) as 'First Day of Last Month',
dbo.udf_GET_DATE_OF('FIRST DAY OF MONTH',@dte) as 'First Day of Month',
dbo.udf_GET_DATE_OF('LAST DAY OF MONTH',@dte) as 'Last Day of Month',
dbo.udf_GET_DATE_OF('LAST DAY OF WEEK',@dte) as 'Last Day of Week',
dbo.udf_GET_DATE_OF('FIRST DAY OF MONTH',@dte) as 'First Day of Week', 
dbo.udf_GET_DATE_OF('LAST WORKING DAY OF MONTH',@dte) as 'Last Working Day of Month',
dbo.udf_GET_DATE_OF('LAST WORKING DAY OF LAST MONTH',@dte) as 'Last Working Day of Last Month',
dbo.udf_GET_DATE_OF('FIRST WEEKDAY OF MONTH',@dte) as 'First Week Day of Month', 
dbo.udf_GET_DATE_OF('LAST WEEKDAY OF MONTH',@dte) as 'Last Week Day of Month'



*/
=============================================================================


CREATE FUNCTION [dbo].[udf_GET_DATE_OF]
(
@rule varchar(30),
@dte datetime
)
RETURNS VARCHAR(30) AS
BEGIN

DECLARE @ret varchar(30)

IF @rule = 'FIRST DAY OF LAST MONTH'
  BEGIN  
 SELECT @ret = '01/' + UPPER(LEFT(DATENAME(MONTH,DATEADD(MONTH,-1,@dte)),3)) + '/' + CAST(YEAR(  DATEADD(MONTH,-1,@dte)  ) as varchar(4))
  END
ELSE IF @rule = 'FIRST DAY OF WEEK'
BEGIN
 SELECT @ret = DATEADD(dd,-(DATEPART(dw, @dte) - 1),@dte)
END
ELSE IF @rule = 'LAST DAY OF WEEK'
BEGIN
 SELECT @ret = DATEADD(dd,-(DATEPART(dw, @dte) - 7),@dte)
END
ELSE IF @rule = 'FIRST DAY OF MONTH'
BEGIN
 SELECT @ret = DATEADD(dd,-(DAY(@dte)-1),@dte)
END
ELSE IF @rule = 'LAST DAY OF MONTH'
BEGIN
 SELECT @ret = DATEADD(d, -DAY(DATEADD(m,1,@dte)),DATEADD(m,1,@dte))
END
ELSE IF @rule = 'FIRST WEEKDAY OF MONTH'
BEGIN
 SELECT @ret = DATENAME(dw, DATEADD(dd, - DATEPART(dd, @dte) + 1, @dte))
END
ELSE IF @rule = 'LAST WEEKDAY OF MONTH'
BEGIN
 SELECT @dte = DATEADD(dd,-(DAY(@dte)-1),DATEADD(MONTH,1,@dte)),
   @ret = DATENAME(dw,CONVERT(VARCHAR, DATEADD(DAY, 0 - ((DATEPART(DAY, @dte)) +
     CASE WHEN DATENAME(WEEKDAY, DATEADD(DAY, 0 - (DATEPART(DAY, @dte)), @dte)) = 'SUNDAY' THEN 2
       WHEN DATENAME(WEEKDAY, DATEADD(DAY, 0 - (DATEPART(DAY, @dte)), @dte)) = 'SATURDAY' THEN 1
       ELSE 0 END
     ), @dte), 113))

       END
ELSE IF @rule = 'LAST WORKING DAY OF LAST MONTH'
BEGIN
   SELECT @ret =  CONVERT(VARCHAR, DATEADD(DAY, 0 - ((DATEPART(DAY, @dte)) +
      CASE WHEN DATENAME(WEEKDAY, DATEADD(DAY, 0 - (DATEPART(DAY, @dte)), @dte)) = 'SUNDAY' THEN 2
        WHEN DATENAME(WEEKDAY, DATEADD(DAY, 0 - (DATEPART(DAY, @dte)), @dte)) = 'SATURDAY' THEN 1
        ELSE 0 END
      ), @dte), 113)
END
ELSE IF @rule = 'LAST WORKING DAY OF MONTH'
BEGIN
 SELECT @dte = DATEADD(dd,-(DAY(@dte)-1),DATEADD(MONTH,1,@dte)), 
     @ret =  CONVERT(VARCHAR, DATEADD(DAY, 0 - ((DATEPART(DAY, @dte)) +
       CASE WHEN DATENAME(WEEKDAY, DATEADD(DAY, 0 - (DATEPART(DAY, @dte)), @dte)) = 'SUNDAY' THEN 2
         WHEN DATENAME(WEEKDAY, DATEADD(DAY, 0 - (DATEPART(DAY, @dte)), @dte)) = 'SATURDAY' THEN 1
         ELSE 0 END
       ), @dte), 113)
END

RETURN @ret


END

The example usage is given in the UDF definition e.g

DECLARE @dte datetime
SELECT  @dte = GETDATE() --OR pass in a literal date e.g '2011-Jun-03 03:54:00'

SELECT dbo.udf_GET_DATE_OF('FIRST DAY OF LAST MONTH',@dte) as 'First Day of Last Month',
dbo.udf_GET_DATE_OF('FIRST DAY OF MONTH',@dte) as 'First Day of Month',
dbo.udf_GET_DATE_OF('LAST DAY OF MONTH',@dte) as 'Last Day of Month',
dbo.udf_GET_DATE_OF('LAST DAY OF WEEK',@dte) as 'Last Day of Week',
dbo.udf_GET_DATE_OF('FIRST DAY OF MONTH',@dte) as 'First Day of Week', 
dbo.udf_GET_DATE_OF('LAST WORKING DAY OF MONTH',@dte) as 'Last Working Day of Month',
dbo.udf_GET_DATE_OF('LAST WORKING DAY OF LAST MONTH',@dte) as 'Last Working Day of Last Month',
dbo.udf_GET_DATE_OF('FIRST WEEKDAY OF MONTH',@dte) as 'First Week Day of Month', 
dbo.udf_GET_DATE_OF('LAST WEEKDAY OF MONTH',@dte) as 'Last Week Day of Month'

I have found this very useful lately when calculating certain statistical reports and maybe some of you will as well.