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: GO5. 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: GO6. 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: 
No comments:
Post a Comment