SQL SERVER 2005 - Procedure to create Batch with start and end time for particular days

Hi,
This logic will help you to generate the batches for the days for particular time spans and you can use this to generate batches with particular details

Pass the arguments of Start Date,End Date,Start Time,End Time and give the time interval in minutes and it will generate the result



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE ListTheDate

@varFromDate   VARCHAR(50),-- from date
@varToDate VARCHAR(50),-- to date
@varTimePartitionInMinute VARCHAR(50),-- time span in minutes
@PartStartTime VARCHAR(50),-- daily start time
@PartEndTime VARCHAR(50)-- daily end time

AS
BEGIN
DECLARE  @MidDate TABLE
(
MidDates DATETIME,
StartTime DATETIME,
EndTime DATETIME,
Name VARCHAR(50)
);
DECLARE @lCount INTEGER;
SET @lCount =0;
DECLARE @lDays INTEGER;
DECLARE @lTime INTEGER;
DECLARE @lCountTime INTEGER;

SET NOCOUNT ON;

SELECT @lDays=DATEDIFF(day,CONVERT(DATETIME,@varFromDate,103),CONVERT(DATETIME,@varToDate,103));

SELECT @lTime=DATEDIFF(minute,CONVERT(DATETIME,@PartStartTime,108),CONVERT(DATETIME,@PartEndTime,108));
WHILE (@lDays >= @lCount)
BEGIN

SET @lCountTime =0;
WHILE(@lTime > @lCountTime)

BEGIN
INSERT INTO @MidDate
(
MidDates,
StartTime,
EndTime,
Name
)
VALUES
(
DATEADD(day,@lCount,CONVERT(DATETIME,@varFromDate,103)),
DATEADD(minute,@lCountTime,CONVERT(DATETIME,@PartStartTime,108)),
DATEADD(minute,@lCountTime+@varTimePartitionInMinute,CONVERT(DATETIME,@PartStartTime,108)),
'Kamesh'
);

SELECT @lCountTime = @lCountTime + @varTimePartitionInMinute;

END

SELECT @lCount = @lCount + 1;

END

SELECT CONVERT(VARCHAR(15),MidDates,103) AS "Date",
Name,
CONVERT(VARCHAR(15),StartTime,108) AS "Batch Start Time",
CONVERT(VARCHAR(15),EndTime,108) AS "BATCH END TIME"

FROM @MidDate;


END
GO