How can i change dataset cell format

Question

if i use this query thath creates a table(look Table1), But VisitingGap column is not correct format. i used cast method to give format. But not working correctly.i need

Table2

declare @date1 nvarchar(100) , @date2 nvarchar(100) , @countgap int,@count int 
set @date1='2009-05-12'
set @date2 = '2009-05-13'
set @countgap = 30
set @count=48

CREATE TABLE #Temp (VisitingCount int, [Time] int, [Date] datetime )
DECLARE @DateNow DATETIME,@i int,@Time int, @Date datetime
set @DateNow='00:00'  
set @i=1;  
while(@i<@count)  
    begin  
        set @DateNow = DATEADD(minute, @countgap, @DateNow)
        set @Time = (datepart(hour,@DateNow)*60+datepart(minute,@DateNow))/@countgap 
     set @Date = CONVERT(VARCHAR(5),@DateNow, 108)
        insert into #Temp(VisitingCount,[Time],[Date]) values(0,@Time,@Date )
        set @i=@i+1
    end

select 
Sum(VisitingCount) as VisitingCount, [Time], 
Cast([Time]*@countgap/60 as nvarchar(50)) +':'+Cast( [Time]*@countgap%60 as nvarchar(50))
from (
  select 0 as VisitingCount, [Time] from #Temp
  Union All
    select count(page) as VisitingCount, 
    (datepart(hour,Date)*60+datepart(minute,Date))/@countgap as [Time] 
    from scr_SecuristLog
    where Date between @date1 and @date2
    GROUP BY (datepart(hour,Date)*60+datepart(minute,Date))/@countgap
  ) X
group by [Time]
order by  2 asc

---

Table 1 :

---

.

.

.

VCount  Time    VisitingGap

0   1 0:30

0   2 1:0

0   3 1:30

0   4 2:0

0   5 2:30

0   6 3:0

0   7 3:30

0   8 4:0

0   9 4:30

0   10 5:0

.

.

.

---

Table 2 : i need below table !!!!

---

.

.

.

VCount  Time    VisitingGap

0   1 00:30

0   2 01:00

0   3 01:30

0   4 02:00

0   5 02:30

0   6 03:00

0   7 03:30

0   8 04:00

0   9 04:30

0   10 05:00

.

.

.

Look please! i think problem the cast method

Cast([Time]*@countgap/60 as nvarchar(50)) +':'+Cast( [Time]*@countgap%60 as nvarchar.........

Answer 1

when you do the math, you lose the leading zeros, this will add them back when you form the string

EDIT

original code-->           Cast([Time]*@countgap/60 as nvarchar(50))    +':'+           Cast( [Time]*@countgap%60 as nvarchar(50))
changed code--->RIGHT('00'+Cast([Time]*@countgap/60 as varchar(2)  ),2) +':'+RIGHT('00'+Cast( [Time]*@countgap%60 as varchar(2)  ),2)

Answer 2

You have the answer staring you in the face in the while loop :

CONVERT(VARCHAR(5),@DateNow, 108)

Replace @DateNow with [Date] and you've got it.

Answer 3

Instead of casting numerics to strings, try converting to dates and the dates to strings:

CONVERT(CHAR(5), DATEADD(mi, [Time], '1900-01-01'), 108)

Answer 4

What a mess. Use an auxiliary numbers table (avoids the loop) and handle your slot grouping that way. Also note that your use of between can have problems, which is why I always use a >= and < construct.

/*
-- DROP Numbers table if it exists
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Numbers]') AND type in (N'U'))
DROP TABLE [dbo].[Numbers]
GO
-- Now re-create it and fill it with sequential numbers starting at 1
SELECT TOP 10000 IDENTITY(INT,1,1) AS Num
INTO dbo.Numbers
FROM master.INFORMATION_SCHEMA.COLUMNS i1
CROSS JOIN master.INFORMATION_SCHEMA.COLUMNS i2;
GO
-- Add a primary key/clustered index to the numbers table
ALTER TABLE dbo.Numbers
ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Num);
GO
*/

CREATE TABLE #Log (
     page varchar(255) NOT NULL
    ,date datetime NOT NULL
    )

INSERT  INTO #Log
VALUES  (
         'page1'
        ,'2009-05-12 08:30'
        )
INSERT  INTO #Log
VALUES  (
         'page1'
        ,'2009-05-12 08:35'
        )
INSERT  INTO #Log
VALUES  (
         'page2'
        ,'2009-05-12 07:30'
        )
INSERT  INTO #Log
VALUES  (
         'page2'
        ,'2009-05-12 09:35'
        )

DECLARE @date1 datetime
   ,@date2 datetime
   ,@countgap int
   ,@count int 
SET @date1 = '2009-05-12'
SET @date2 = '2009-05-13'
SET @countgap = 30
SET @count = (24 * 60) / @countgap

;
WITH    LogX
          AS (
              SELECT    *
                       ,DATEADD(DAY, -DATEDIFF(DAY, 0, date), date) AS time
              FROM      #Log
              WHERE date >= @date1 AND date < DATEADD(DAY, 1, @date2)
             ),
        Slots
          AS (
              SELECT    Num
                       ,DATEADD(MINUTE, (Num - 1) * @countgap, 0) AS SlotStart
                       ,DATEADD(MINUTE, Num * @countgap, 0) AS SlotEnd
              FROM      Numbers
              WHERE     Num <= @count
             )
    SELECT  Num, CONVERT(varchar(5), SlotEnd, 108) AS [Time], COUNT(page) AS VistingCount
    FROM    Slots
    LEFT JOIN LogX
            ON LogX.TIME >= Slots.SlotStart
               AND LogX.TIME < Slots.SlotEnd
    GROUP BY Num, SlotEnd

DROP TABLE #Log