July 28, 2011 at 1:17 am
Hello,
I have this query:
declare @start as datetime declare @end as datetime
set @start = '2011-07-10 00:00:00'
set @end = '2011-7-16 23:59:59'
select AccountNumber,Name,Inventory,EnterUserName,ProviderName,Prescription,OrderType,
convert(char,EnterDateTime,120)as EnterDateTime,rx.Status,
convert(char,StopDateTime,120)as StopDateTime,Charge
from PhaRx rx
join AdmVisits av on rx.VisitID = av.VisitID
where EnterDateTime between @start and @end
and Inventory in ('PX-ER','PX-EDSOU','EDTRIAG','PX-TRAUMA1','PX-TRAUMA2')
and datepart(hh,EnterDateTime) not between '11' and '20'
order by EnterDateTime
It works great EXCEPT: I need the timeframe not between 1100 and 2130. The above gets me not between 1100 and 2100 effectively, which might be good enough but I want it perfect! But it appears the datepart function cannot do multiple parts any way I think to try. EG- if instead of just "hh" I try "hhmi, hh:mi, (hh)+(mi)" or any other crazy way it always gives me a syntax error.
How might I go about getting hours and minutes in this instance so that I may select 'not between' the exact timeframes (1100-2130) for multiple days? Thanks for your time.
July 28, 2011 at 7:17 am
Now this is not the complete answer, but a bit of T-SQL to illustrate how to "extract" the minutes component from a DATETIME value.
declare @start as datetime declare @end as datetime
DECLARE @min-2 INT -- added when editing original posting
set @start = '2011-07-10 00:00:00'
set @end = '2011-7-16 23:59:59'
--Using 3 selects just to illustrate what is happening
SELECT RIGHT(CONVERT(VARCHAR(20),@end,100),7)
SELECT LEFT(RIGHT(CONVERT(VARCHAR(20),@end,100),7),5)
-- Final select as a single statment
SELECT RIGHT(LEFT(RIGHT(CONVERT(VARCHAR(20),@end,100),7),5),2)
-- Edited to add implicit conversion of minutes to interger
SET @min-2 = (SELECT RIGHT(LEFT(RIGHT(CONVERT(VARCHAR(20),@end,100),7),5),2))
Hope this is enough to assist you in achieving the final solution.
July 28, 2011 at 7:24 am
The statement below shows you how to return the numbers of minutes, so you could use this in your where clause where the number of minutes was not between 690 and 1290
declare @start as datetime
declare @end as datetime
set @end = '20110710 20:59:00'
select datediff(mi, dateadd(dd, datediff(dd, 0, @end), 0), @end)
July 29, 2011 at 4:29 am
Thanks guys. I looked at those suggestions for a good while trying different things, but couldn't figure out how to utilize them quite like I hoped. I spent a few hours looking at them and trying "case when" stuff and "exists" amongst other things. To replace the 1 line dealing with the timeframe, I finally came up with this:
and exists (select EnterDateTime where datepart(hh,EnterDateTime) not between '11' and '20')
and not exists (select EnterDateTime
where datepart(hh,EnterDateTime) = '21'
and datepart(mi,EnterDateTime) between '00' and '30')
That actually gets me what I need, that is every time of every day selected except between 1100-2130. It doesn't look anything like what you guys had, but I guess it works, so that's good no? Do you see any major problems with it? It's kind of backward-like thinking to me but it worked...
Have I mentioned I'm not a DBA or even in IS/IT but just a humble little pharmacist? I just try to do this stuff for my hospital to help the pharmacy w/ info gathering because I think it's fun and enjoy it. But I don't think I'll ever really think efficiently like SQL code/syntax, it seems pretty confusing. Maybe if I did something would be wrong with me... 😀
Thanks for your help.
July 29, 2011 at 8:39 am
pharmboy4u
Have I mentioned I'm not a DBA or even in IS/IT but just a humble little pharmacist? I just try to do this stuff for my hospital to help the pharmacy w/ info gathering because I think it's fun and enjoy it. But I don't think I'll ever really think efficiently like SQL code/syntax, it seems pretty confusing. Maybe if I did something would be wrong with me...
Thanks for your help.
Thanks for your response to us. Now be careful, working with T-SQL is like an addictive drug. Once started you can not stop.
If other problems/questions pop up come on back and many will attempt to help you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply