January 25, 2013 at 10:01 am
Hello,
I would like to locate the records within specific timeframe from a Friday prior week to last Friday. For example, Let's say today is Saturday, January 26, 2013.
I would like to obtain the record from Friday, January 18, 2013 at 6:01 p.m. to Friday, January 25, 2013 at 6:00 p.m.
(MS SQL Statement)....
.... and
a.[starttime] >= convert(datetime,left(convert(varchar, DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) -3, 121),4+1+2+1+2) + ' 18:00') and
a.[starttime] < convert(datetime,left(convert(varchar, DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) +4, 121),4+1+2+1+2) + ' 18:00')
My question is that: is this way efficient and providing the results? After using it, I felt like it converted into the string format. I think, sometimes, I saw the time of 21:00 or 22:00 popped up in the records as well, even though the value of 18 on the later week is less than 21 or 22.
Please do not suggest any Public function. We would like to embed a short statement into a query. That's all. Thanks.
January 25, 2013 at 10:12 am
No, that's not very efficient (any conversions to varchar in a date function should be suspect).
DECLARE @CurrentDate DATETIME
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @CurrentDate = GETDATE()
SELECT @StartDate = DATEADD(hh,18,DATEADD(dd,4,DATEADD(wk, DATEDIFF(wk,0,@CurrentDate)-1, 0)))
SELECT @EndDate = DATEADD(ww,1,@StartDate)
SELECT @StartDate, @EndDate -- so you can check values
.... rest of query here ---
WHERE a.starttime > @StartTime AND a.endtime <= @EndTime
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 25, 2013 at 10:58 am
Thank you very much. This should work as well.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply