CODEWITHSUNDEEP
sql-serverdatetime
user3353723
user3353723

Reputation: 219

Auto-populating a table with DateTime values in SQL server

I have a table called Appointment in which I want to display appointment slots (60min intervals) from 10am to 3pm for Weekdays till August.

Below is my table structure:

AppointmentID
Date
StartTime
EndTime

Sample record:

1
2015-2-2
11:45:34
12:45:34

I know I can do Insert statements manually but I wanted to write a SQL query that would do that automatically and would appreciate your help.

Upvotes: 0

Views: 312

Answers (2)

JohnLBevan
JohnLBevan

Reputation: 24410

Try this:

insert Appointment([Date],StartTime,EndTime)
select cast(DATEADD(day, n, StartTime) as date)
, cast(StartTime as time)
, cast(dateadd(hour,1,DATEADD(day, n, StartTime)) as time) 
from
(
    select DATEADD(hour, n, '2015-01-01 10:00') StartTime
    from dbo.generate_series(0, DATEDIFF(hour,'2015-01-01 10:00','2015-01-01 15:00'), 1, 0)
) StartTimes
cross join dbo.generate_series(0, DATEDIFF(day,'2015-01-01','2015-08-31'), 1, 0)
order by AppointmentStart

Uses generate_series from: https://developer42.wordpress.com/2014/09/17/t-sql-generate-series-getting-a-list-of-numbers-in-a-given-range/

create function dbo.generate_series
(
      @start bigint
    , @stop bigint
    , @step bigint = 1
    , @maxResults bigint = 0 --0 = unlimited
)
returns @results table(n bigint)
as
begin

    --avoid infinite loop (i.e. where we're stepping away from stop instead of towards it)
    if @step = 0 return
    if @start > @stop and @step > 0 return
    if @start < @stop and @step < 0 return

    --ensure we don't overshoot
    set @stop = @stop - @step

    --treat negatives as unlimited
    set @maxResults = case when @maxResults < 0 then 0 else @maxResults end

    --generate output
    ;with myCTE (n,i) as
    (
        --start at the beginning
        select @start
        , 1
        union all
        --increment in steps
        select n + @step
        , i + 1
        from myCTE 
        --ensure we've not overshot (accounting for direction of step)
        where (@maxResults=0 or i<@maxResults)
        and
        (
               (@step > 0 and n <= @stop)
            or (@step < 0 and n >= @stop)
        )  
    )
    insert @results
    select n 
    from myCTE
    option (maxrecursion 0) --sadly we can't use a variable for this; however checks above should mean that we have a finite number of recursions / @maxResults gives users the ability to manually limit this 

    --all good  
    return

end

Upvotes: 1

RKS
RKS

Reputation: 1410

You might want to consider using the DATEADD in your insert statements.

Upvotes: 1

Related Questions