Hi Lynn,

Forgot to explain how to add data.

i want to add data for particular day selected by user. Example if user want to add data for Only Monday or friday etc.

I am able to find day using below query. but not able to add in database table.

IF OBJECT_ID('TempDB..#test','U') IS NOT NULL

drop table #test

create table #test

(studentid int,

studentname varchar(20),

startdate datetime,

enddate datetime,

starttime datetime, endtime datetime

)

insert into #test

select 1, 'john', '2013-05-01 00:00:00', '2013-05-31 00:00:00', '2013-05-01 10:00:00', '2013-05-31 14:00:00'

select studentid, studentname, dateadd(day, b.n-1, startdate)

, RIGHT(CONVERT(VARCHAR(100),dateadd(day, b.N-1, DATEADD(mi, (a.N-1)*15, starttime)),0),7) as starttime

, RIGHT(CONVERT(VARCHAR(100),dateadd(day, b.N-1, DATEADD(mi, a.N*15, starttime)),0),7) as endtime

, datename(dw,dateadd(day, b.N-1, DATEADD(mi, (a.N-1)*15, starttime)))

from #test

cross join Tally A

cross join Tally B

where

a.N >= 1 and a.N <= datediff(mi, starttime, endtime)/15 and

b.N >= 1 and b.N <= DATEDIFF(day, startdate,enddate) + 1