Monday, April 29, 2013

Handy Date Functions

These date functions are especially useful for BI and reporting type scenarios. Used properly they can simply SQL code making it much, much easier to read. Used properly they can also make SQL queries perform better!!!

First let’s install the date functions by running the following code snippets:

1. The first day of the month

   1: SET ANSI_NULLS ON
   2: GO
   3: SET QUOTED_IDENTIFIER ON
   4: GO
   5: -- =============================================
   6: -- Author:        William Brown
   7: -- Create date: 2013-04-29
   8: -- Description:    Return the first day of the month
   9: -- For more information check out
  10: -- http://yetanothercomputingblog.blogspot.com.au/
  11: -- =============================================
  12: CREATE FUNCTION fn_FirstDateOfMonth(@date DATETIME)
  13: RETURNS DATE
  14: AS
  15: BEGIN
  16:     
  17:     DECLARE @first_of_month DATE
  18:  
  19:     SELECT @first_of_month = CONVERT(DATE,DATEADD(dd,-(DAY(@date)-1),@date))
  20:  
  21:     RETURN @first_of_month
  22:  
  23: END
  24: GO
  25:  

2. The first day of the next month



   1: SET ANSI_NULLS ON
   2: GO
   3: SET QUOTED_IDENTIFIER ON
   4: GO
   5: -- =============================================
   6: -- Author:            William Brown
   7: -- Create date:        2013-04-29
   8: -- Description:        Return the first day of the next month
   9: -- For more information check out
  10: -- http://yetanothercomputingblog.blogspot.com.au/
  11: -- =============================================
  12: CREATE FUNCTION fn_FirstDateOfNextMonth(@date DATETIME)
  13: RETURNS DATE
  14: AS
  15: BEGIN
  16:     
  17:     DECLARE @first_of_month DATE
  18:  
  19:     SELECT @first_of_month = CONVERT(DATE,DATEADD(dd,-(DAY(DATEADD(mm,1,@date))-1),DATEADD(mm,1,@date)))
  20:  
  21:  
  22:     RETURN @first_of_month
  23:  
  24: END
  25: GO
  26:  

3. The first day of the year



   1: SET ANSI_NULLS ON
   2: GO
   3: SET QUOTED_IDENTIFIER ON
   4: GO
   5: -- =============================================
   6: -- Author:            William Brown
   7: -- Create date:        2013-04-29
   8: -- Description:        Return the first day of the year
   9: -- For more information check out
  10: -- http://yetanothercomputingblog.blogspot.com.au/
  11: -- =============================================
  12: CREATE FUNCTION fn_FirstDateOfYear(@date DATETIME)
  13: RETURNS DATE
  14: AS
  15: BEGIN
  16:     
  17:     DECLARE @first_day_of_year DATE
  18:  
  19:     SELECT @first_day_of_year = DATEADD(YEAR, DATEDIFF(YEAR, 0, @date), 0)
  20:  
  21:     RETURN @first_day_of_year
  22:  
  23: END
  24: GO
  25:  

4. The first day of the next year



   1: SET ANSI_NULLS ON
   2: GO
   3:  
   4: SET QUOTED_IDENTIFIER ON
   5: GO
   6:  
   7: -- =============================================
   8: -- Author:            William Brown
   9: -- Create date:        2013-04-29
  10: -- Description:        Return the first day of the next year
  11: -- For more information check out
  12: -- http://yetanothercomputingblog.blogspot.com.au/
  13: -- =============================================
  14: CREATE FUNCTION [dbo].[fn_FirstDateOfNextYear](@date DATETIME)
  15: RETURNS DATE
  16: AS
  17: BEGIN
  18:     
  19:     DECLARE @first_day_of_next_year DATE
  20:  
  21:     SELECT @first_day_of_next_year = DATEADD(YEAR, DATEDIFF(YEAR, 0,DATEADD(YEAR,1,@date)), 0)
  22:  
  23:     RETURN @first_day_of_next_year
  24:  
  25: END
  26:  
  27: GO

5. The first day of the Australian financial year (1 Jul XXXX)



   1: SET ANSI_NULLS ON
   2: GO
   3:  
   4: SET QUOTED_IDENTIFIER ON
   5: GO
   6:  
   7: -- =============================================
   8: -- Author:            William Brown
   9: -- Create date:        2013-04-29
  10: -- Description:        Return the first day of the next year
  11: -- For more information check out
  12: -- http://yetanothercomputingblog.blogspot.com.au/
  13: -- =============================================
  14: CREATE FUNCTION [dbo].[fn_FirstDateOfFinancialYear](@date DATETIME)
  15: RETURNS DATE
  16: AS
  17: BEGIN
  18:     
  19:     DECLARE @first_day_of_financial_year DATE
  20:  
  21:     SELECT @first_day_of_financial_year = DATEADD(MM,6,DATEADD(YEAR, DATEDIFF(YEAR, 0, @date), 0))
  22:  
  23:     RETURN @first_day_of_financial_year
  24:  
  25: END
  26:  
  27: GO

6. The first day of the next Australian financial year (1 Jul XXXX)



   1: SET ANSI_NULLS ON
   2: GO
   3: SET QUOTED_IDENTIFIER ON
   4: GO
   5: -- =============================================
   6: -- Author:            William Brown
   7: -- Create date:        2013-04-29
   8: -- Description:        Return the first day of the next year
   9: -- For more information check out
  10: -- http://yetanothercomputingblog.blogspot.com.au/
  11: -- =============================================
  12: CREATE FUNCTION fn_FirstDateOfNextFinancialYear(@date DATETIME)
  13: RETURNS DATE
  14: AS
  15: BEGIN
  16:     
  17:     DECLARE @first_day_of_next_financial_year DATE
  18:  
  19:     SELECT @first_day_of_next_financial_year = DATEADD(MM,6,DATEADD(YEAR, DATEDIFF(YEAR, -1, @date), 0))
  20:  
  21:     RETURN @first_day_of_next_financial_year
  22:  
  23: END
  24: GO
  25:  

Running the following code:



   1: set nocount on
   2: select dbo.fn_FirstDateOfMonth(getdate()) AS FirstDateOfMonth
   3: select dbo.fn_FirstDateOfNextMonth(getdate()) AS FirstDateOfNextMonth
   4: select dbo.fn_FirstDateOfYear(getdate()) AS FirstDateOfYear
   5: select dbo.fn_FirstDateOfNextYear(getdate()) AS FirstDateOfNextYear
   6: select dbo.fn_FirstDateOfFinancialYear(getdate()) AS FirstDateOfFinancialYear
   7: select dbo.fn_FirstDateOfNextFinancialYear(getdate()) AS FirstDateOfNextFinancialYear

Gives the following results:



   1: FirstDateOfMonth
   2: ----------------
   3: 2013-04-01
   4:  
   5: FirstDateOfNextMonth
   6: --------------------
   7: 2013-05-01
   8:  
   9: FirstDateOfYear
  10: ---------------
  11: 2013-01-01
  12:  
  13: FirstDateOfNextYear
  14: -------------------
  15: 2014-01-01
  16:  
  17: FirstDateOfFinancialYear
  18: ------------------------
  19: 2013-07-01
  20:  
  21: FirstDateOfNextFinancialYear
  22: ----------------------------
  23: 2014-07-01
  24: