Split date/time range into day ranges Using this data (also shown in this fiddle):

create table dbo.records (
  id int not null primary key identity(1, 1),
  startdatetime datetime not null,
  enddatetime datetime not null
);

insert into dbo.records (startdatetime, enddatetime)
values ('2014-01-01 12:00:00', '2014-01-05 12:00:00'),
 ('2014-01-10 12:00:00', '2014-01-15 12:00:00');


What query could I use to get the result set below?

2014-01-01 12:00:00   2014-01-02 00:00:00
2014-01-02 00:00:00   2014-01-03 00:00:00
2014-01-03 00:00:00   2014-01-04 00:00:00
2014-01-04 00:00:00   2014-01-05 00:00:00
2014-01-05 00:00:00   2014-01-05 12:00:00
2014-01-10 12:00:00   2014-01-11 00:00:00
2014-01-11 00:00:00   2014-01-12 00:00:00
2014-01-12 00:00:00   2014-01-13 00:00:00
2014-01-13 00:00:00   2014-01-14 00:00:00
2014-01-14 00:00:00   2014-01-15 00:00:00
2014-01-15 00:00:00   2014-01-15 12:00:00


I would also like the solution to be relatively simple to swap between day/month/year ranges. i have included a month fiddle and a [year fiddle](sqlfiddle.com/#!3/ab1d7/1 fiddles) these show example data and expected results.
OK, I've refactored (hopefully for the last time) for your new data and to use a calendar table.  In this simple demo I create and populate the calendar table (for years 2000 to 2030), then do the day|month|year code.  This version honours the time component and will scale well.  The calendar table is one I use in my dev.

Please note this is intended as a demo, so please double-check the results:

USE tempdb
GO

IF NOT EXISTS ( SELECT * FROM sys.schemas WHERE name = 'calendar' )
EXEC('CREATE SCHEMA calendar')
GO

-- DROP TABLE calendar.main
IF OBJECT_ID('calendar.main') IS NULL
BEGIN

    CREATE TABLE calendar.main (
        calendarId      INT IDENTITY(1,1) NOT NULL,
        calendarDate    DATE NOT NULL,
        calendarDay     INT NOT NULL,
        calendarMonth   INT NOT NULL,
        calendarYear    INT NOT NULL,
        [dayOfYear]     INT NOT NULL,
        [week]          INT NOT NULL,
        [quarter]       INT NOT NULL,
        datenameDay     VARCHAR(9) NOT NULL,
        datenameMonth   VARCHAR(9) NOT NULL,
        academicMonth   INT NOT NULL,
        academicYear    INT NOT NULL,
        isWeekday       BIT NULL,
        isWeekend       BIT NULL,
        lastDayOfMonth  BIT NULL,

    CONSTRAINT PK_calendar__main PRIMARY KEY ( calendarDate ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
    CONSTRAINT UK_calendar__main UNIQUE NONCLUSTERED ( calendarId ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]


    -- Add indexes
    CREATE INDEX idx_calendar__main__calendarYear ON calendar.main ( calendarYear )
    CREATE INDEX idx_calendar__main__calendarMonth ON calendar.main ( calendarMonth )
    CREATE INDEX idx_calendar__main__calendarDay ON calendar.main ( calendarDay )
    CREATE INDEX idx_calendar__main__calendarYearMonthDay ON calendar.main ( calendarYear, calendarMonth, calendarDay )

    CREATE INDEX idx_calendar__main__academicYear ON calendar.main ( academicYear )
    CREATE INDEX idx_calendar__main__academicMonth ON calendar.main ( academicMonth )
    CREATE INDEX idx_calendar__main__academicYearMonth ON calendar.main ( academicYear, academicMonth )

    --!!TODO add any indexes you need

    ALTER TABLE calendar.main ADD  DEFAULT ((0)) FOR isWeekday
    ALTER TABLE calendar.main ADD  DEFAULT ((0)) FOR isWeekend
    ALTER TABLE calendar.main ADD  DEFAULT ((0)) FOR lastDayOfMonth
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  ((academicMonth&gt;=(1) AND academicMonth&lt;=(12)))
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  ((academicYear&gt;=(1999) AND academicYear&lt;=(2030)))
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  ((calendarDay&gt;=(1) AND calendarDay&lt;=(31)))
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  ((calendarMonth&gt;=(1) AND calendarMonth&lt;=(12)))
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  ((calendarYear&gt;=(2000) AND calendarYear&lt;=(2030)))
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  (([dayOfYear]&gt;=(1) AND [dayOfYear]&lt;=(366)))
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  (([quarter]&gt;=(1) AND [quarter]&lt;=(4)))
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  (([week]&gt;=(1) AND [week]&lt;=(54)))

END
GO


-- Populate calendar table once only
IF NOT EXISTS ( SELECT * FROM calendar.main )
BEGIN

    -- Populate calendar table
    WITH cte AS
    (
    SELECT 0 x
    UNION ALL
    SELECT x + 1
    FROM cte
    WHERE x &lt; 11323 -- Do from year 1 Jan 2000 until 31 Dec 2030 (extend if required)
    )
    INSERT INTO calendar.main ( calendarDate, calendarDay, calendarMonth, calendarYear, [dayOfYear], [week], [quarter], datenameDay, datenameMonth, academicMonth, academicYear, isWeekday, isWeekend, lastDayOfMonth )
    SELECT
        calendarDate,
        DAY( calendarDate ) cDay, 
        MONTH( calendarDate ) cMonth, 
        YEAR( calendarDate ) cYear, 
        DATENAME( dayOfYear, calendarDate ) cdayOfYear, 
        DATENAME( week, calendarDate ) cWeek, 
        DATENAME( quarter, calendarDate ) cQuarter, 
        DATENAME( dw, calendarDate ) cdw, 
        DATENAME( month, calendarDate ) cMonthName,
        MONTH( academicDate ) aMonth,
        YEAR( academicDate ) aYear,
        CASE WHEN DATENAME( dw, calendarDate ) Not In ( 'Saturday', 'Sunday' ) THEN 1 ELSE 0 END,
        CASE WHEN DATENAME( dw, calendarDate ) In ( 'Saturday', 'Sunday' ) THEN 1 ELSE 0 END,
        CASE WHEN DAY( DATEADD( day, 1, calendarDate ) ) = 1 THEN 1 ELSE 0 END

    FROM
        (
        SELECT 
            DATEADD( day, x, '1 Jan 2000' ) calendarDate,
            DATEADD( month, -7, DATEADD( day, x, '1 Jan 2000' ) ) academicDate
        FROM cte
        ) x
    WHERE calendarDate &lt; '1 Jan 2031'
    OPTION ( MAXRECURSION 0 )


    ALTER INDEX ALL ON calendar.main REBUILD

END
GO



IF OBJECT_ID('dbo.records') IS NOT NULL DROP TABLE dbo.records
CREATE TABLE dbo.records (
    Id              INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    period          VARCHAR(10) NOT NULL,
    startDateTime   DATETIME NOT NULL,
    endDateTime     DATETIME NOT NULL
)
GO

INSERT INTO dbo.records ( period, startDateTime, endDateTime )
VALUES
    ( 'day', '2014-01-01 06:10:10', '2014-01-05 18:20:30' ),
    ( 'day', '2014-01-10 04:40:20', '2014-01-15 02:50:10' ),
    ( 'day', '2014-01-11 04:10:10', '2014-01-11 08:00:10' ),

    ( 'month', '2014-01-05 14:20:30', '2014-06-05 12:30:40' ),
    ( 'month', '2014-10-10 06:50:40', '2014-12-15 04:10:20' ),
    ( 'month', '2014-10-10 06:10:50', '2014-10-10 06:30:10' ),

    ( 'year', '2015-03-05 14:10:30', '2018-02-06 05:20:40'),
    ( 'year', '2019-02-04 12:32:23', '2022-03-09 22:34:12'),
    ( 'year', '2020-04-05 06:05:04', '2020-06-03 09:07:45')

GO

DECLARE @period VARCHAR(10) = 'year'    -- 'day'|'month'|'year'

IF @period = 'day'
BEGIN

    SELECT
        r.Id,
        r.startDateTime,
        ISNULL( CAST( c.calendarDate AS DATETIME ), r.endDateTime ) AS endDateTime
    FROM dbo.records r
        OUTER APPLY ( SELECT TOP 1 * FROM calendar.main c WHERE c.calendarDate Between r.startDateTime And r.endDateTime ) c
    WHERE r.period = 'day'

    UNION ALL

    SELECT
        r.Id,
        CAST ( c.calendarDate AS DATETIME ) startDateTime,
        CASE WHEN c2.calendarDate &gt; endDateTime THEN endDateTime ELSE c2.calendarDate END
    FROM dbo.records r
        INNER JOIN calendar.main c ON c.calendarDate Between r.startDateTime And r.endDateTime
            CROSS APPLY ( SELECT TOP 1 * FROM calendar.main c2 WHERE c2.calendarDate &gt; c.calendarDate ) c2
    WHERE r.period = 'day'
    ORDER BY Id, startDateTime, endDateTime

END
ELSE IF @period = 'month'
BEGIN

    SELECT
        r.Id,
        r.startDateTime,
        ISNULL( CAST( c.calendarDate AS DATETIME ), r.endDateTime ) AS endDateTime
    FROM dbo.records r
        OUTER APPLY ( 
            SELECT TOP 1 * 
            FROM calendar.main c 
            WHERE c.calendarDate Between r.startDateTime And r.endDateTime 
              AND c.calendarDay = 1
              ) c
    WHERE r.period = 'month'

    UNION ALL

    SELECT
        r.Id,
        CAST ( c.calendarDate AS DATETIME ) startDateTime,
        CASE WHEN c2.calendarDate &gt; endDateTime THEN endDateTime ELSE c2.calendarDate END
    FROM dbo.records r
        INNER JOIN calendar.main c ON c.calendarDate Between r.startDateTime And r.endDateTime
            CROSS APPLY ( SELECT TOP 1 * FROM calendar.main c2 WHERE c2.calendarDate &gt; c.calendarDate AND c2.calendarDay = 1 ) c2
    WHERE r.period = 'month'
      AND c.calendarDay = 1
    ORDER BY Id, startDateTime, endDateTime

END

ELSE IF @period = 'year'
BEGIN

    SELECT
        r.Id,
        r.startDateTime,
        ISNULL( CAST( c.calendarDate AS DATETIME ), r.endDateTime ) AS endDateTime
    FROM dbo.records r
        OUTER APPLY ( 
            SELECT TOP 1 * 
            FROM calendar.main c 
            WHERE c.calendarDate Between r.startDateTime And r.endDateTime 
              AND c.dayOfYear = 1
              ) c
    WHERE r.period = 'year'

    UNION ALL

    SELECT
        r.Id,
        CAST ( c.calendarDate AS DATETIME ) startDateTime,
        CASE WHEN c2.calendarDate &gt; endDateTime THEN endDateTime ELSE c2.calendarDate END
    FROM dbo.records r
        INNER JOIN calendar.main c ON c.calendarDate Between r.startDateTime And r.endDateTime
            CROSS APPLY ( SELECT TOP 1 * FROM calendar.main c2 WHERE c2.calendarYear &gt; c.calendarYear 
                AND c2.dayOfYear = 1 ) c2
    WHERE r.period = 'year'
      AND c.dayOfYear = 1
    ORDER BY Id, startDateTime, endDateTime

END

ELSE
    RAISERROR( 'Invalid period specified (%s)', 16, 1, @period )
GO