Help with SQL- difference between dates\time
-
Hi, I'm hoping someone can help me with an sql statement. I am using brio with an import of data. I need to make a custom column using an sql statement to figure out the difference between 2 different dates/time. I don't know SQL.
Here's what I would like ( or something close )
response date response deadline date
12/31/03 04:44 AM 12/31/03 08:12 AM
01/14/04 09:43 PM 01/15/04 09:43 PM
If the response date is greater than the deadline date then I would like the results to read
-1:45 (meaning deadline was missed by 1 hour 45 min)
If it the response date is less than the deadline date then the results would read +1:45
-
You'll want to use DATEPART to write this, good information about DATEPART can be found in Books Online (BOL).
Note that it will take a bit of coding to format it the way you want it, but then there's no quick way to work with dates in T-SQL.
Everett Wilson
ewilson10@yahoo.com -
Actually DATEDIFF will work for finding the different between two dates. See BOL for formatting.
-
Even DATEDIFF can be hard to use here, since it reports the number of "time boundaries" crossed between two dates. For example, if a deadline was missed by 1 hour 45 minutes, then running DATEDIFF on days would return 0, running DATEDIFF on hours would return either 1 or 2 (it would return 2, for example, if the deadline was 1:50 PM and the actual time was 3:05 PM - 1 hour and 15 minutes difference, but two hour boundaries were crossed), and running DATEDIFF on minutes would return 105.
Another useful approach (instead of lots of DATEDIFFs) might be to just use date math. For instance, it is now about 6:10 PM in my time zone. Check out the following query:
select getdate() as Now, cast(getdate() - '9/7/2004' as decimal(10,5)) as "Days Late"
Now Days Late ------------------------ ----------- 2004-09-07 18:10:36.383 0.75737
This simply means that about 75 percent of my day has passed. But the trick will work for any two datetimes:
declare @t1 datetime, @t2 datetime
set @t1 = convert(datetime, '2004-08-31 23:50:00', 120) set @t2 = convert(datetime, '2004-09-01 01:05:00', 120)
select @t1 as t1, @t2 as t2, cast(@t2 - @t1 as decimal(10, 5)) as "Days Late"
t1 t2 Days Late ------------------------ ----------------------- ------------ 2004-08-31 23:50:00.000 2004-09-01 01:05:00.000 .05208
Again, the interpretation is that t2 is 5.208 percent of a day later than t1. Now, if you want to know how many hours that is, just multiply .05208 times 24 to get 1.25. If you want to know how many minutes that is, multiply .05208 times 1440 (the number of minutes in a day) to get 75. And no, the datatype I cast to (decimal (10,5)) doesn't matter ... in fact, float is probably a better choice here, but that would up with some numbers in scientific notation, and I wanted to force something else just to tidy up the display here.
Make sense? I think this is a lot nicer way to handle all those interval-type problems in T-SQL. Besides, I love to follow Steve's posts, hoping that some day he'll help me get a nice job out in the mountains someplace, just to get me to quit lurking here ...
Cheers,
Chris
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply