Problem with a partitioning switch script
@wBob got it in one. It's all about those NULLs.

The following script creates the Destination table with a NOT NULL modified column and it alters the same column on the TableName_Partition2 to be NOT NULL after creation.

This allows the SWITCH to run.

CREATE TABLE [dbo].[SourceTable](
    [Col1] [bigint] NULL,
    [Col2] [int] NULL,
    [Col3] [int] NULL,
    [Col4] [int] NULL,
    [Col5] [int] NULL,
    [Col6] [money] NULL,
    [Modified] [datetime] NULL,
    [ModifiedBy] [varchar](50) NULL,
    [ModifiedType] [char](1) NULL
); 

CREATE CLUSTERED INDEX [SourceTable_Modified] ON [dbo].[SourceTable]
(   [Modified] ASC   )
GO


CREATE PARTITION FUNCTION [ThirteenMonthPartFunction](datetime) AS 
 RANGE RIGHT FOR VALUES 
    (   N'2015.08.01',
        N'2015.09.01',
        N'2015.10.01',
        N'2015.11.01',
        N'2015.12.01',
        N'2016.01.01',
        N'2016.02.01',
        N'2016.03.01',
        N'2016.04.01',
        N'2016.05.01',
        N'2016.06.01',
        N'2016.07.01',
        N'2016.08.01'
 );

CREATE PARTITION SCHEME [ThirteenMonthPartScheme] 
    AS PARTITION [ThirteenMonthPartFunction] ALL TO ([PRIMARY]);

CREATE TABLE [dbo].[DestinationTable](
    [Col1] [bigint] NULL,
    [Col2] [int] NULL,
    [Col3] [int] NULL,
    [Col4] [int] NULL,
    [Col5] [int] NULL,
    [Col6] [money] NULL,
    [Modified] [datetime] NOT NULL,
    [ModifiedBy] [varchar](50) NULL,
    [ModifiedType] [char](1) NULL
) ON ThirteenMonthPartScheme (Modified);
GO

CREATE CLUSTERED INDEX [DestinationTable_Modified] ON [dbo].[DestinationTable]
(
    [Modified] ASC
) ON ThirteenMonthPartScheme (Modified);
GO

INSERT [dbo].[SourceTable]
VALUES  (1,1,1,1,1,9.99, '2015.08.01', 'James', 'A'),
        (2,1,1,1,1,9.99, '2015.08.02', 'James', 'A'),
        (3,1,1,1,1,9.99, '2015.08.03', 'James', 'A'),
        (4,1,1,1,1,9.99, '2015.09.01', 'James', 'A'),
        (5,1,1,1,1,9.99, '2015.09.02', 'James', 'A'),
        (6,1,1,1,1,9.99, '2015.09.03', 'James', 'A'),
        (7,1,1,1,1,9.99, '2015.10.01', 'James', 'A'),
        (8,1,1,1,1,9.99, '2015.10.02', 'James', 'A')

SELECT * 
    INTO [TableName_Partition2]
FROM  SourceTable
WHERE Modified &gt;= N'2015.08.02'
  AND Modified &lt; N'2015.09.01';
GO

ALTER TABLE [TableName_Partition2] ALTER COLUMN Modified datetime NOT NULL
GO

CREATE CLUSTERED INDEX [TableName_Modified]  
    ON [dbo].[TableName_Partition2] ([Modified] ASC);  
GO

ALTER TABLE [dbo].[TableName_Partition2] 
    ADD CONSTRAINT [CK_TableName_Partition2] 
    CHECK ([Modified] &gt;= N'2015.08.01'
      AND  [Modified] &lt; N'2015.09.01');
GO

ALTER TABLE [TableName_Partition2] 
    SWITCH TO [DestinationTable] PARTITION 2;
GO