CODEWITHSUNDEEP
c#.netasp.netsql-serverasp.net-mvc-2
SRA
SRA

Reputation: 1691

Increment Time with 1 minute and 5 minute intervals using c# - if possible with sql server too

I've a requirement in which I have a start and End time. For instance let us assume the start time as 10:00 and end time as 17:30. I need increment this in 1 minute and 5 minute intervals.

For example if I click the button named "Increment 5 mins" the series should be like this,

10:00 10:05 10:10

...

17:30

if I click the button " incrment 1 min", then the series should be

10:00 10:01 10:02 .....

17:30.

It would be really appreciated if some one can explain me with code how to implement this using c# and also how to write a stored procedure to implement the same( SQL Server)

Upvotes: 1

Views: 4013

Answers (3)

Adriaan Stander
Adriaan Stander

Reputation: 166346

You would use either a WHILE loop in Sql Server or a recursive CTE in Sql Server 2005+

CTE

DECLARE @StartTime DATETIME,
        @EndTime DATETIME,
        @Inc INT

SELECT  @StartTime = '10:00',
        @EndTime = '17:30',
        @Inc = 1

;WITH Vals AS (
        SELECT  @StartTime RunTime
        UNION   ALL
        SELECT  DATEADD(mi,@Inc,RunTime) 
        FROM    Vals
        WHERE   DATEADD(mi,@Inc,RunTime) <= @EndTime
)
SELECT  *
FROm    Vals
OPTION (MAXRECURSION 0)

For C# I would implement something simple like

C#

DateTime startTime = DateTime.Today.AddHours(10);
DateTime endTime = DateTime.Today.AddHours(17).AddMinutes(30);
int inc = 1;
List<DateTime> timeList = new List<DateTime>();
while (startTime < endTime)
{
    timeList.Add(startTime);
    startTime = startTime.AddMinutes(inc);
}
timeList.Add(endTime);

or even

DateTime startTime = DateTime.Today.AddHours(10);
DateTime endTime = DateTime.Today.AddHours(17).AddMinutes(30);
List<DateTime> timeEnumList = new List<DateTime>();
Enumerable.Range(0, (int)(endTime.Subtract(startTime).TotalMinutes / inc) + 1).ToList().ForEach(i => timeEnumList.Add(startTime.AddMinutes(i)));

Upvotes: 2

Ron Savage
Ron Savage

Reputation: 11079

I have a SQL Server CTE function that generates time intervals between a starting and ending date time value that you can use ... it is explained here.

Upvotes: 0

user215054
user215054

Reputation:

In C# its the TimeSpan class. You can use it in arithmetics with "+" or "-" against DateTime objects.

Upvotes: 0

Related Questions