Database

Ashfield wrote:

Your STUFF is wrong.

stuff(stuff(CAST(DayTransTime AS char(8)),5,0,':'),3,0,':')

should do it.

obarahmeh wrote:

I used your query, but it did not work

CREATE TABLE  #MyTable (   DayTransDate Decimal(8,0) ,    DayTransTime  Decimal(6,0) );  
INSERT INTO #MyTable (DayTransDate, DayTransTime) SELECT 20080709, 120844 ; 
INSERT INTO #MyTable (DayTransDate, DayTransTime) SELECT 20090709, 120844 ;   
INSERT INTO #MyTable (DayTransDate, DayTransTime) SELECT 20080710, 150844 ;
INSERT INTO #MyTable (DayTransDate, DayTransTime) SELECT 20080710, 200844 ;

SELECT *,DATEADD(minute, -30, getdate()) as comparedate  FROM #MyTable
WHERE convert(datetime, cast(DayTransDate AS char(8)) 
+ ' ' + stuff(stuff(CAST(DayTransTime AS char(8)),5,0,':'),3,0,':'))
> DATEADD(minute, -30, getdate());

CREATE TABLE  #MyTable ( 
  DayTransDate Decimal(8,0) ,  
  DayTransTime  Decimal(6,0) 
); 
 
INSERT INTO #MyTable (DayTransDate, DayTransTime) SELECT 20080709, 120844 ; 
INSERT INTO #MyTable (DayTransDate, DayTransTime) SELECT 20090709, 120844 ; 
 
 
SELECT *  
, DayTransDate * 1000000 + DayTransTime  
, convert(datetime, cast(DayTransDate AS char(8))  
  + ' '  
  + STUFF(STUFF(CAST(DayTransTime AS char(6)), 5, 0, ':'), 3, 0, ':'))  
, CONVERT(NVARCHAR, DATEADD(minute, -30, GETDATE()), 112) +  
  REPLACE(CONVERT(NVARCHAR, DATEADD(minute, -30, GETDATE()), 108) , ':', '')  
, REPLACE(REPLACE(REPLACE( 
  CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()), 120)  
  , '-', '') , ':', '') , ' ', '')  
 
FROM #MyTable  
WHERE  
  DayTransDate * 1000000+DayTransTime  
  >  
  REPLACE(REPLACE(REPLACE( 
    CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()), 120)  
    , '-', '') , ':', '') , ' ', '' 
  )  
; 
 
 
SELECT * FROM #MyTable 
WHERE  
  CONVERT( 
    NVARCHAR(19),  
    convert(datetime,  
      cast(DayTransDate AS char(8))  
      + ' '  
      + STUFF(STUFF(CAST(DayTransTime AS char(6)), 5, 0, ':'), 3, 0, ':') 
      )  
  , 120)  
  >   
  CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()) , 120) 
; 

SELECT * FROM #MyTable  
WHERE  
   DayTransDate * 1000000 + DayTransTime  
   >  
   CONVERT(NVARCHAR,  
     DATEADD(minute, -30, GETDATE()), 112 
   )  
   +  
   REPLACE( 
     CONVERT(NVARCHAR, DATEADD(minute, -30, GETDATE()), 108)  
     , ':', '' 
   )  
; 
 
SELECT * FROM #MyTable 
WHERE  
  DayTransDate * 1000000 + DayTransTime  
  > 
  REPLACE(REPLACE(REPLACE( 
    CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()), 120)  
    , '-', '') , ':', '') , ' ', '' 
  )  
; 
 
 
DROP TABLE #MyTable 

SELECT * FROM MyTable 
WHERE 
  CONVERT( 
    NVARCHAR(19) 
    , convert( 
       datetime, 
       cast( 
         DayTransDate AS char(8))  
         + ' '  
         + STUFF(STUFF(CAST(DayTransTime AS char(6)), 5, 0, ':'), 3, 0, ':')) ,  
       120 
    )  
  >   
  CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()) , 120) 
; 
 
SELECT * FROM MyTable 
WHERE DayTransDate * 1000000 + DayTransTime  
> CONVERT( 
   NVARCHAR,  
    DATEADD(minute, -30, GETDATE()),  
    112 
  )  
  + REPLACE(CONVERT(NVARCHAR, DATEADD(minute, -30, GETDATE()), 108) , ':', '')  
;  

SELECT * FROM MyTable
WHERE 
  DayTransDate * 1000000 + DayTransTime 
  > 
  REPLACE(REPLACE(REPLACE(
      CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()), 120) 
      , '-', '') , ':', '') , ' ', ''
  ) 
;

haneef wrote:

i know therotic about index for retrieve data fast.

how it work ?.

WITH XMLNAMESPACES ('uri1' as media, 'uri2' as live)
SELECT RTRIM(Title) AS 'media:title'
   , [Description]  AS 'media:description'
   , Url            AS 'live:link'
   , DateCreated    AS 'media:pubdate'
   , thumbnail      AS 'media:thumbnail' 
FROM dbo.Videos FOR XML PATH('item'), ROOT('root')

Select last_insert_id()<br />

Select SCOPE_IDENTITY()<br />