Cacher is the code snippet organizer for pro developers

Как получить начало и конец суток:

declare @dateStart datetime, @FromDate datetime, @initDate datetime, @dateEnd datetime, @ToDate datetime
--set @initDate = '2012-12-31 15:05:00'
set @initDate = getdate()

-- получаем дату начала суток
set @FromDate = (convert(datetime, convert(varchar, @initDate, 101)))
set @dateStart = DATEADD(DAY, DATEDIFF(DAY, 0, @initDate), 0)

-- получаем дату окончания суток
set @ToDate = (convert(datetime, convert(varchar, @initDate, 101) + ' 23:59:59'))
-- получаем дату начала следующего дня
set @dateEnd = DATEADD(DAY,1+DATEDIFF(DAY,0,@initDate),0)

select @Fromdate, @dateStart
select @ToDate, @dateEnd

-- Variant 1

DECLARE @S varchar(max),
  @Split char(1),
  @X xml

SELECT @S = '1,2,3,4,5',
  @Split = ','

SELECT @X = CONVERT(xml,'<root><s>' + REPLACE(@S,@Split,'</s><s>') + '</s></root>')

SELECT [Value] = T.c.value('.','varchar(20)')
FROM @X.nodes('/root/s') T(c)

-- Variant 2
declare @string nvarchar(500)
declare @pos int
declare @piece nvarchar(500)
declare @strings table(string nvarchar(512))

SELECT @string = '101,28,256'

if right(rtrim(@string),1) <> ','
   SELECT @string = @string  + ','

SELECT @pos =  patindex('%,%' , @string)

while @pos <> 0 
begin
 SELECT @piece = left(@string, (@pos-1))

 --you now have your string in @piece
 insert into @strings(string) values ( cast(@piece as nvarchar(512)))

 SELECT @string = stuff(@string, 1, @pos, '')
 SELECT @pos =  patindex('%,%' , @string)
end

SELECT * FROM @Strings