CODEWITHSUNDEEP
sqlsql-server
Imran Qadir Baksh - Baloch
Imran Qadir Baksh - Baloch

Reputation: 33944

SQL for Query Less than or Equal to 16 Hour 30 Minutes?

I have the following SQL,

SELECT dims_branchcode, 
       dims_documentname, 
       dims_branchname, 
       Count(dims_documentname) AS NoofRequestWithinCutoff, 
       track.sentdate 
FROM   track 
       INNER JOIN td_wf_dims394 
               ON track.documentno = td_wf_dims394.sno 
WHERE  ( track.indexcardname = 'TD_WF_DIMS394' 
         AND track.sender = 0 
         AND Datepart(hh, senttime) <= 16 ) 
GROUP  BY dims_branchcode, 
          dims_branchname, 
          dims_documentname, 
          sentdate 
ORDER  BY track.sentdate

which is working great for 16 hour. But I need 16 hour and 30 minutes how to specify hour as well as minutes

Upvotes: 1

Views: 2286

Answers (1)

TechDo
TechDo

Reputation: 18629

Add the condition where 990 is (16*60) + 30 minutes

AND DATEDIFF(mi, DATEADD(dd, 0, DATEDIFF(dd, 0, senttime)), senttime)<=990

instead of

AND Datepart(hh, senttime) <= 16

Check with sample data:

declare @tbl as table(dts datetime)

insert into @tbl values 
('2012-01-07 17:27'), 
('2012-01-07 16:27'), 
('2012-02-22 11:36')

select * From @tbl

--condition datepart(hh, dts)<=16 and datepart(minute, dts)<=30, which doesnot include the 3rd row
select * From @tbl where datepart(hh, dts)<=16 and datepart(minute, dts)<=30 

--gives the desired result
select * From @tbl where DATEDIFF(mi, DATEADD(dd, 0, DATEDIFF(dd, 0, dts)), dts)<=990

Upvotes: 4

Related Questions