Click here to Skip to main content
15,867,308 members
Home / Discussions / Database
   

Database

 
QuestionConvert Decimal into Date Query?? Pin
obarahmeh9-Jul-08 20:30
obarahmeh9-Jul-08 20:30 
AnswerRe: Convert Decimal into Date Query?? Pin
Paddy Boyd9-Jul-08 20:56
Paddy Boyd9-Jul-08 20:56 
AnswerRe: Convert Decimal into Date Query?? Pin
Ashfield9-Jul-08 21:07
Ashfield9-Jul-08 21:07 
GeneralRe: Convert Decimal into Date Query?? Pin
obarahmeh9-Jul-08 22:35
obarahmeh9-Jul-08 22:35 
GeneralRe: Convert Decimal into Date Query?? [modified] Pin
Ashfield10-Jul-08 1:27
Ashfield10-Jul-08 1:27 
GeneralRe: Convert Decimal into Date Query?? Pin
obarahmeh10-Jul-08 2:02
obarahmeh10-Jul-08 2:02 
GeneralRe: Convert Decimal into Date Query?? Pin
Ashfield10-Jul-08 9:22
Ashfield10-Jul-08 9:22 
AnswerRe: Convert Decimal into Date Query?? [modified] Pin
leoinfo10-Jul-08 2:44
leoinfo10-Jul-08 2:44 
/* Hi

Play with this:

*/
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 



/* Any of the following should work the same way for your case: */


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) 
      , '-', '') , ':', '') , ' ', ''
  ) 
;


modified on Thursday, July 10, 2008 9:00 AM

GeneralRe: Convert Decimal into Date Query?? [modified] Pin
obarahmeh12-Jul-08 21:46
obarahmeh12-Jul-08 21:46 
Questionhow to use index? very simple i want some basic information........... Pin
Shaik Haneef9-Jul-08 20:27
Shaik Haneef9-Jul-08 20:27 
AnswerRe: how to use index? very simple i want some basic information........... Pin
Paddy Boyd9-Jul-08 20:54
Paddy Boyd9-Jul-08 20:54 
AnswerRe: how to use index? very simple i want some basic information........... Pin
Ashfield9-Jul-08 21:13
Ashfield9-Jul-08 21:13 
QuestionTransaction (Process ID 156) was deadlocked on lock Pin
sujithkumarsl9-Jul-08 18:23
sujithkumarsl9-Jul-08 18:23 
AnswerRe: Transaction (Process ID 156) was deadlocked on lock Pin
Ashfield9-Jul-08 21:22
Ashfield9-Jul-08 21:22 
Questiondatabase conection_string Pin
nelsonpaixao9-Jul-08 13:56
nelsonpaixao9-Jul-08 13:56 
AnswerRe: database conection_string Pin
Paddy Boyd9-Jul-08 20:59
Paddy Boyd9-Jul-08 20:59 
AnswerRe: database conection_string Pin
Ashfield9-Jul-08 21:17
Ashfield9-Jul-08 21:17 
QuestionSQL FOR XML issue [modified] Pin
DerekFL9-Jul-08 7:59
DerekFL9-Jul-08 7:59 
AnswerRe: SQL FOR XML issue Pin
leoinfo9-Jul-08 9:26
leoinfo9-Jul-08 9:26 
QuestionDataTable validation event Pin
benallen0029-Jul-08 7:54
benallen0029-Jul-08 7:54 
AnswerRe: DataTable validation event Pin
DerekFL9-Jul-08 8:05
DerekFL9-Jul-08 8:05 
QuestionGetting the value of a primary key that is an Identity field Pin
LongRange.Shooter9-Jul-08 6:26
LongRange.Shooter9-Jul-08 6:26 
AnswerRe: Getting the value of a primary key that is an Identity field Pin
leoinfo9-Jul-08 6:56
leoinfo9-Jul-08 6:56 
QuestionCan't find SSIS or SSRS Pin
meeram3959-Jul-08 0:12
meeram3959-Jul-08 0:12 
AnswerRe: Can't find SSIS or SSRS Pin
Jerry Hammond9-Jul-08 3:03
Jerry Hammond9-Jul-08 3:03 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.