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?