sql server - How to get the result table by parsing column values? -

sql server - How to get the result table by parsing column values? -

i have table 1 column (datelist, varchar(4000)) contains list of date-time values strings. looks following:

2014-09-26 19:00 2014-09-27 19:00 2014-09-28 19:00 2014-09-29 19:00

how can result table same structure, datelist column should have values in range:

dateitem >= mindate , dateitem <= maxdate

it stored procedure.

the code i've used:

declare @sstring varchar(4000) declare @splitchar char(1) declare @count int declare @datelength int declare @xmlstring xml set @splitchar = ',' set @count = 1 set @datelength = 17 select @sstring = datelist t_action id = 44404 print @sstring while @count <= len(@sstring) / 17 - 1 begin set @sstring = stuff(@sstring, @datelength, 0, ',') set @datelength = 17 + @datelength + 1 set @count = @count + 1 end --use xml extract date. set @xmlstring = convert(xml,'<root><s>' + replace(@sstring,@splitchar,'</s><s>') + '</s></root>') create table #temp (dateval varchar(20)) insert #temp (dateval) select datevalue (select t.c.value('.','varchar(max)') datevalue @xmlstring.nodes('/root/s') t(c)) temp datevalue between '2014-09-27' , '2014-09-30' select stuff((select ' ' + dateval dateval #temp xml path(''), type).value('.', 'varchar(max)'), 1, 2, '') expectedresult if object_id(n'tempdb..#temp', n'u') not null drop table #temp; go

it take longer time figure out expected result.

i think requirement show column of string having dates between min , max dates column of string having dates.

run below stored procedure , pass string of dates, min , max dates.

create procedure getdate (@sstring varchar(4000), @mindate datetime, @maxdate datetime) begin declare @splitchar char(1) declare @count int declare @datelength int declare @xmlstring xml set @splitchar = ',' set @count = 1 set @datelength = 17 set @sstring = rtrim(@sstring) + ' ' while @count <= len(@sstring) / 17 - 1 begin set @sstring = stuff(@sstring, @datelength, 0, ',') set @datelength = 17 + @datelength + 1 set @count = @count + 1 end --use xml extract date. set @xmlstring = convert(xml,'<root><s>' + replace(@sstring,@splitchar,'</s><s>') + '</s></root>') create table #temp (dateval varchar(20)) insert #temp (dateval) select datevalue (select t.c.value('.','varchar(max)') datevalue @xmlstring.nodes('/root/s') t(c)) temp datevalue between @mindate , @maxdate select ltrim((select dateval dateval #temp xml path(''), type).value('.', 'varchar(max)')) expectedresult end

to execute stored procedure.

exec getdate '2014-09-26 19:00 2014-09-27 19:00 2014-09-28 19:00 2014-09-29 19:00', '2014-09-27 19:00', '2014-09-28 19:00' param 1 : string of dates. param 2 : min date. param 3 : max date. expectedresult ---------------------------------------------------- 2014-09-27 19:00 2014-09-28 19:00

sql-server