I am creating an sql job and i need to have propper time values to accomplis my goal.

I will be running this job every 7 days and i will be inserting a set of dates into a table as long as the date is less than todays date + 7 days.

I also want to add these dates at speific times. The start of my day should be at 09:00 am and the end of the day shaould be at 17:00pm each insert should be 15 minutes apart and must never go pass the end time

my code so far:

    CREATE PROCEDURE TimeslotPublication

    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

        DECLARE @Today date = GETDATE()
        DECLARE @WeekAhead datetime


        SET @WeekAhead = dateadd(day,+7,@Today)

        DECLARE @startTime datetime
        DECLARE @endTime datetime

        WHILE @Today < @WeekAhead
        BEGIN

        SET @startTime = '2015-10-02 09:00' --- todays date at 09:00. needs to be set to @today but at the specific time, how do i accomplish this?
        SET @endTime = '2015-10-02 17:00' ---- todays date at 17:00. needs to be set to @today but at the specific time, how do i accomplish this?

        WHILE @startTime < @endTime
        BEGIN

        INSERT INTO TimeSlots (Id, Start, [End],EmployeeId) VALUES(@@IDENTITY,@startTime, @endTime, 123)

        SET @startTime = '2015-10-02 09:15' ---- increase by 15 minutes needs to be set to @today but with a 15min increase, how do i accomplish this?

        END

        SET @Today = dateadd(day,+1,@Today)

        END
GO

Obviosly I do not want to use hardcoded values and SQL is not my strong side. How can i Accomplish this.

NOTE I use "WHILE" here, i am not sure if this is the best way to do this and appreciate any advice.

EDIT: @starttime and @endTime need to be set to @today but at a specific time. and then i also need to increase the time by 15 minutes.My question is simply how do i implement this correctly in tsql?

Related posts

Recent Viewed