July 20, 2007 at 11:02 am
Hi,
I am trying to write a query that only shows records where there is a time difference between two columns of exactly 24 hours, and was hoping someone knows what the best way to do this would be. This what I have so far...
SELECT
id, datevalue1, datevalue2
FROM tbl1
where datediff(hour, datepart(hour, datevalue1), datepart(hour, datevalue2)) = 24
and datediff(dd, datepart(dd, datevalue1), datepart(dd, datevalue2)) = 1
and
datediff(mi, datepart(mi, datevalue1), datepart(mi, datevalue2)) = 0
July 20, 2007 at 11:15 am
Why not do this?
WHERE DATEADD(dd, -1, datevalue1) = datevalue2
July 20, 2007 at 11:17 am
How exact do you want to become? Why not measure the difference in seconds and see if it is exactly 3600*24 or 86400? But you might want to give a second or two for good measure. Thus
where datediff(s,datevalue1,datevalue2) between 86395 and 86405
July 20, 2007 at 1:25 pm
Thank you Michael, you solution worked best for me. I was also able to come up with this, which gave me the same result.
SELECT id, datevalue1, datevalue2
FROM tbl1
WHERE convert(decimal(19, 5), datevalue2 - datevalue1) = 1
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply