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:  

Enumerate All Default Constraints In A Database

The following script will enumerate all default constraints in the database the script is run against.

select c .name from

sys.all_columns a

inner join sys.tables b on a.object_id = b .object_id

inner join sys.default_constraints c on a. default_object_id = c .object_id

Enumerate All SQL Jobs with Alerts

The following SQL code will enumerate all jobs on a SQL Server that have alerts associated with the job. It will also list the operator to be notified and the email address used in the notification.

use msdb

select j. name, o .name, o.email_address

from sysjobs j

join sysoperators o on j.notify_email_operator_id = o. id

order by j.name asc

Install HyperV And Your Networking Dies!!!

It sounds dramatic - when you install the Hyper-V role on Windows you may find that you cannot reach your HyperV host remotely any more. It worked before the Hyper-V role was added and now the server is unreachable. Fortunately it's a rather simple problem to fix.

The cause of the problem is that when you install Hyper-V it completely reconfigures your networking. Firstly, it will create a "Network Bridge" which is used for to provide virtual networking to your virtual machines. The "Network Bridge" completely takes over your network card. In it's place a virtual network card is installed on your Hyper-V host for host communications.

So the first step is to confirm your network settings on the virtual network card which is typically labelled vEthernet (Followed by the physical network card details).

In my case these settings were all fine. My problem resided in my virtual network card settings. First right click on the virtual network card and go into it's properties.

Next click on the configure button just below the network card.

In the advanced tab select the IPv4 Large Send Offload settings and set it as disabled.