April 7, 2006 at 10:15 am
ok, check this out. Just looking at the hour now. I want the same hour. So, this works and brings me in valid results
select calldatetime, tz.stop1 from history h
inner join areacodes ac on substring(phonenum,1,3) = cast(ac.areacode as varchar(3))
inner join timezones tz on ac.timezoneid=tz.timezoneid
where calldatetime > getdate()-1
and datepart(hour, calldatetime) = datepart(hour, tz.stop1)
results
---------
2006-04-05 20:30:17.000 1899-12-30 20:00:00.000
2006-04-06 20:21:42.000 1899-12-30 20:00:00.000
2006-04-05 20:15:06.000 1899-12-30 20:00:00.000
2006-04-05 20:11:29.000 1899-12-30 20:00:00.000
2006-04-05 20:11:54.000 1899-12-30 20:00:00.000
2006-04-05 20:14:19.000 1899-12-30 20:00:00.000
but this does not bring in any results!
select calldatetime, tz.stop1 from history h
inner join areacodes ac on substring(phonenum,1,3) = cast(ac.areacode as varchar(3))
inner join timezones tz on ac.timezoneid=tz.timezoneid
where calldatetime > getdate()-1
and datediff(hour, tz.stop1, h.calldatetime) = 0
---------------------------------------------------------------------------------------------
select datepart(minute, stop1) from timezones
return only 11 records
but this:
select calldatetime, tz.stop1 from history h
inner join areacodes ac on substring(phonenum,1,3) = cast(ac.areacode as varchar(3))
inner join timezones tz on ac.timezoneid=tz.timezoneid
where calldatetime > getdate()-1
and datepart(hour, calldatetime) = datepart(hour, tz.stop1)
returns a ton of records like it should be doing
April 7, 2006 at 10:33 am
datediff returns the difference between two datetime values.
It seems to me the timezone table contains just hours (no dates!) and that's why you won't get the expected results.
In order to convince yourself, just run the statement select datediff(hour, getdate()-1, getdate()) [you should get 24].
April 7, 2006 at 12:11 pm
Ok, I had the same argument though. I told my boss that there are no dates. But we're not looking at the date portion....there are indeeed valid times if you see...
April 7, 2006 at 12:12 pm
1899-12-30 20:00:00.000 is 8pm
we have other values like 1899-12-30 21:00:00.000 in that column
April 7, 2006 at 12:17 pm
calldatetime however have valid dates, example 2006-04-07 09:06:52.000
where
calldatetime > getdate()-2
so this part is fine, nothing to worry about there, that's not what I'm working on..I'm working on the time portion, the calldatetime which is 5 minutes after stop1's time at the same hour
example
calldatetime stop1
2006-04-07 09:06:52.000 1899-12-30 21:00:00.000
2006-04-06 15:39:43.000 1899-12-30 21:00:00.000
2006-04-06 18:10:18.000 1899-12-30 21:00:00.000
2006-04-06 18:10:51.000 1899-12-30 21:00:00.000
2006-04-06 20:35:39.000 1899-12-30 21:00:00.000
2006-04-06 20:50:24.000 1899-12-30 21:00:00.000
2006-04-06 15:32:04.000 1899-12-30 20:00:00.000
2006-04-06 17:07:38.000 1899-12-30 20:00:00.000
2006-04-06 17:11:56.000 1899-12-30 20:00:00.000
2006-04-06 14:56:00.000 1899-12-30 20:00:00.000
for the check whether calldatetime is 5 minutes more than stop1, we're only looking at the time portion anyway so what is your concern about the dates...who cares if they are not valid, we are not checking against the dates, just the time portion of stop1 and only the date portion is not really something to be concerned about.
April 7, 2006 at 6:21 pm
select datediff( hour, '1899-12-30 20:00:00.000','2006-04-05 20:14:19.000')
Returns: 931488
The number of hours between the two dates. That, is why the query you posted returns 0 records when you used datediff(hour, tz.stop1, h.calldatetime) = 0
if you need to check time parts only Make the Day the same and then use datediff with the hours
Like
datediff( hour, '1900-01-01 ' + convert(varchar(12),tz.stop1,114), '1900-01-01 ' + convert(varchar(12),h.calldatetime,114)
HTH
* Noel
April 8, 2006 at 9:55 am
Would substring be allot slower then datdiff?
e.g.
substring(date1,12,2) = substring(date2,12,2)
I am assuming that we are trying to find rows in one table that are within the same hour as the other table. e.g. 10:15 same as 10:00
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply