Inserting value into table variable in SQL Server 2008

Question

I am declaring a table variable as below.

declare @TableVar table ( interval  Time not null)

I need to insert values as time starting from '8:00 am' to '8:00 pm' with an interval, of say 30 min, into this table variable.

I need the table variable with data as below

8:00 am
8:30 am
9:00 am
.
.
.
12:00 pm
12.30 pm
.
.
7:00 pm
7:30 pm
8:00 pm

This time interval of 30 min is derived from a field DMCDur(int) from a table.

DECLARE @intFlag INT
SET @intFlag = (select D.DMCDur from doctor_master D where D.doc_id=3)

Basically, I need to query a table to get the column DMCDur which can be: 30, 20, 15, etc. Representing: 30 min, 20 min, 15 min, respectively.

I need to set the start time as 8:00 am and need to add DMCDur with this start time and generate a set of time with interval as DMCDur and insert these values into the table variable I mentioned above.

My final aim is to join this set of time with another table data and populate a grid. So thought of going in this way.

Please suggest your ideas on this.Sample stored procedure for the same will be appreciated. Note:Joining variable in the other table is a DATETIME variable(eg 2012-08-06 08:00:00.000).So the table variable should also be DATETIME as datatype so then I can join both tables with this time.

What if DMCDur doesn't perfectly divide the total time period - should the last time be earlier or greater than 8pm? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Aug 6, 2012 at 8:48
I just need to make the time period with DMCDur as time interval and last time should be earlier than 8 pm. — Soumya
– Soumya, Commented Aug 6, 2012 at 8:58

dani herrera · Accepted Answer · 2012-08-06 11:42:45Z

2

You should use a row generator. I use Itzik Ben Gan's Row Generator:

create table #TableVar  ( interval  Time not null) 

declare @elapsed int
declare @from_time time, 
        @to_time time
select @elapsed = 30, 
       @from_time = '08:00:00', 
       @to_time = '20:00:00'

;WITH 
Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 ),
D ( n ) as (SELECT ROW_NUMBER()  OVER (ORDER BY n) FROM Nbrs  ),
all_times as (
  SELECT 
     dateadd( minute, ( n - 1) * @elapsed, @from_time) as [a_time]
  FROM 
     D
  where
     n <= ( 12 * 60.0 / @elapsed ) + 1
)
insert 
into #TableVar 
select * from all_times

Results:

;select * from #TableVar


interval 
-------- 
08:00:00 
08:30:00 
09:00:00 
09:30:00 
...
19:00:00 
19:30:00 
20:00:00

* Edited * due OP change requirements:

You can cast datetime to time to get time part:

create table #dates  ( some_date  dateTime not null) 

insert into #dates values 
( '2012-01-01 07:30:00' ),
( '2012-01-01 08:00:00' ),
( '2012-01-01 08:30:00' ),
( '2012-01-01 09:00:00' );


select           d.*, t.*
from             #dates d 
left outer join  #TableVar t 
             on  cast( d.some_date as time ) = t.interval;

Results:

some_date             interval 
-------------         -------- 
2012-01-01 07:30:000  _NULL_        
2012-01-01 08:00:000  8:00:00 
2012-01-01 08:30:000  8:30:00 
2012-01-01 09:00:000  9:00:00

edited Aug 6, 2012 at 11:42

answered Aug 6, 2012 at 9:10

dani herrera

52.1k11 gold badges133 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Soumya Over a year ago

Thanks for the answer.I need to join this data with a datetime field in another table,which is like '2012-08-06 08:00:00.000' where date is current date.Sorry for missing this part in the first post.How can I have the table variable as datetime with current date,so that I can join with the other table to yield a combined result?

dani herrera Over a year ago

@Soumya, I have edited my answer to match your new requirements.

Joe G Joseph · Accepted Answer · 2012-08-06 09:39:38Z

0

try this:

DECLARE @intFlag INT
SET @intFlag =30

declare @start_time time='08:00:00'
declare @end_time time='20:00:00'
declare @t table (date_time datetime)

insert into @t
select DATEADD(mi,number*@intFlag,@start_time) as [time]
from master..spt_values where type='p'
and number<=12*(60/@intFlag)

select * from @t

Edit1: as per your final note:

 select * from @t join <other table>
 on convert(time,date_time)=convert(time,<othertableColumn >)

edited Aug 6, 2012 at 9:39

answered Aug 6, 2012 at 9:00

Joe G Joseph

24.2k5 gold badges58 silver badges59 bronze badges

Collectives™ on Stack Overflow

Inserting value into table variable in SQL Server 2008

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related