Tuesday, 5 July 2011

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.

No comments:

Latest Cheap Amazon Goods