January 14, 2009 at 10:42 am
--This part of the query returns incidents that were created between 6:30am and 6pm for a range of dates, (@StartDateTime and @EndDateTime) and were closed within 15 minutes of creation. Works great! But, keep scrolling...
AND (
(((convert(char(8), workitem_created_on, 114)) >= '06:30:00' and (convert(char(8), workitem_created_on, 114) < '18:00:00'))
and datediff(n, workitem_created_on, workitem_modified_on) <= 15
--and datediff(dd, workitem_created_on, getdate()) = 1
and workitem_created_on between @StartDateTime and @EndDateTime)
or
/*This section is supposed to return incidents that were (created after 6pm on day 1 or before 6:30am on day 2)AND closed by 6:45am on day 2 for the same range of dates. I can't figure out how to write this for the range of dates between @StartDateTime and @EndDateTime.
For example, if I entered ('1-1-09 00:00' and '1-10-09 00:00') as my date range, then I'd need to report tickets that were created on '12-31-08 17:00' and closed on '1-1-09 06:40' in addition to those created on '1-8-09 08:00' and closed on '1-8-09 08:15'. I hope this makes sense. */
(
(
(convert(char(8), workitem_created_on, 114) >= '18:00:00' and datediff(dd, workitem_created_on, getdate()) = 1)
or
(convert(char(8), workitem_created_on, 114) < '06:30:00' and datediff(dd, workitem_created_on, getdate()) = 0)
)
and convert(char(8), workitem_modified_on, 114) <= '06:45:00' and datediff(dd, workitem_modified_on, getdate()) = 0
)
)
January 14, 2009 at 11:17 am
You can do direct math on datetime data.
Why not use:
workitem_created_on >= @StartDateTime and workitem_created_on < @EndDateTime
You just have to make sure that @StartDateTime is the date, and time, you want to start from.
For example, if you want 9 Jan 09 6:30 PM to 10 Jan 09 6:00 AM, then set @StartDateTime to 9 Jan 09 6:30 PM and @EndDateTime to 10 Jan 6:00 AM.
select @StartDateTime = '20090109T18:30:00.000', @EndDateTime = '20090110T06:00:00.000'
or
select @StartDateTime = '9 Jan 09 6:30 PM', @EndDateTime = '10 Jan 09 6:00 AM'
(You'll have to make sure the date for the second version is formatted correctly for your server.)
If all you have is the date, without the time, you can use DateAdd to get the hours and minutes on there.
That will simplify your Where clause significantly.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 14, 2009 at 12:23 pm
The only issue with doing it this way is that I need to be able to input different dates from a gui as oppose to hard coding a Start Date and End Date.
Any other ideas?
January 14, 2009 at 12:47 pm
Hi DC,
Do something like this so that you can use input values for your start datetime
declare @startInput datetime
set @startInput = '2008-01-01'
declare @start datetime
set @start = cast(
cast(year(@startInput) as varchar(4)) + '-' + cast(month(@startInput) as varchar(2))
+ '-' + cast(day(@startInput) as varchar(2)) + ' 18:30:00' as datetime)
select @start
B
January 14, 2009 at 12:50 pm
DC (1/14/2009)
The only issue with doing it this way is that I need to be able to input different dates from a gui as oppose to hard coding a Start Date and End Date.Any other ideas?
That's why I mentioned that you can use DateAdd to fix date-only inputs.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 14, 2009 at 12:51 pm
Yes, I've already made my declarations.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply