Counting Date Time differences
-
I am working on a report where I need to figure out the time difference between the created date and the delivery date. I know how to do that. But also what I need to do is then count how many of those are less than an hour and a half difference. I have tried doing this several different ways but I cant seem to get it. Right now this is what i have for my code:
SELECT Count(*)
FROM trWOHead A WITH (NOLOCK) WHERE Priority like '%RUSH%' and (Createdate>'6/9/2009' and Createdate<'6/11/2009') and (
(SELECT(DateDiff(dd, DeliveryDT, createdate)) As days,
(DateDiff(hh, DeliveryDT, createdate)) % 24 As hours,
(DateDiff(mi, DeliveryDT, createdate)) % 60 As mins
from trwohead
Where days='00' and hours<='1' and mins<='30'))
I think I am getting close... if anyone can see anything let me know please! -
I have altered my code to look like the following.
select Count(*)
FROM trWOHead WITH (NOLOCK)
WHERE Priority like '%RUSH%' and (Createdate>'6/9/2009' and Createdate<'6/11/2009') and (
(SELECT(DateDiff(dd, createdate, DeliveryDT)) As days,
(DateDiff(hh, createdate, DeliveryDT) % 24) As hours,
(DateDiff(mi, createdate, DeliveryDT) % 60) As mins
from trwohead
Where (DateDiff(dd, createdate, DeliveryDT)<1) and ((DateDiff(hh, createdate, DeliveryDT) % 24)<=1) and ((DateDiff(mi, createdate, DeliveryDT) % 60)'6/9/2009' and Createdate<'6/11/2009') ))
-
Hi, jacobostop.
I once had the same problem with you.
When designing a report, we often need a lot of date time functions. However, most reporting tools only provide some common functions as follows: functions today(), now() which are used to get current date or time, and functions year(), month(), day(), hour(), minute() or second() which are used to get corresponding year, month, day, hour, minute or second from a date time.
However, that' s far from enough.
But now, we have a better choice -- RAQ Report. It provides much more date time functions specially to meet our needs. As a result, complex problems like the ones below can be solved easily:
1. Calculate age accurately.
2. Calculate the beginning or the end of a time period.
3. Calculate the difference between two dates or two time points.
A graphic illustration is shown below:
I wish this can help. Good luck to you.
-
Are you sure you want to use NOLOCK clause in your query. This may give inconsistent results at times 😉
Pradeep Singh -
Hey, without some test data and table structures it's hard to provide a decent, tested solution, but I think you're overcomplicating things here a bit... why the nolock, why the subquery?
-- I needed some sample data to test this, not sure about what types you are using, so have made assumptions:
DECLARE @trwohead TABLE (ID INT IDENTITY(1, 1), Priority VARCHAR(10), Createdate DATETIME, DeliveryDT DATETIME)
INSERT INTO @trwohead (Priority, Createdate, DeliveryDT)
SELECT 'RUSHED', '2009/9/7', '2009/9/7 00:30' UNION ALL
SELECT 'RUSHED', '2009/11/5', '2009/11/5 00:30' UNION ALL
SELECT 'RUSHED', '2009/11/6', '2009/11/6 00:30' UNION ALL
SELECT 'PRIORITY', '2009/11/6', '2009/11/6 00:30' UNION ALL
SELECT 'RUSHED', '2009/11/6', '2009/11/6 02:30'
-- Notice I am using a table variable, you'll need to drop @ character, and make sure you test if it works with your data!
SELECT * FROM @trwohead
-- Here is query for getting the count I think you are after:
SELECT COUNT(*)
FROM @trwohead
WHERE Priority like '%RUSH%' AND Createdate>'2009/9/6' AND Createdate<'2009/11/6' -- I've used unambiguous date types here
AND ABS(DATEDIFF(MINUTE, Createdate, DeliveryDT)) < 90 -- I've taken the ABS as I'm not sure if Createddate is always before DeliveryDT or not?
If you post your own structures and sample data, you are likely to get more applicable solutions, specific to your situation.
Have a look at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for an informative article on posting questions in a hassle free way 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply