Minute comparison

and datediff(minute, convert(varchar, calldatetime, 114), convert(varchar, tz.stop1, 114)) >= 5
and datediff(hour, convert(varchar, calldatetime, 114), convert(varchar, tz.stop1, 114)) = 0

it pulls up nothing in my query but should so I think my comparison syntax is off or not right.
LVL 1
dba123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TimCotteeHead of Software ServicesCommented:
Hi dba123,

Why convert the datetimes to varchar when you need to run datediff on datetime datatypes?
and datediff(minute, calldatetime, tz.stop1, ) >= 5
and datediff(hour, calldatetime,tz.stop1) = 0

Tim Cottee
0
Aneesh RetnakaranDatabase AdministratorCommented:
Hi dba123,

Why u r converting the to varchar, u can continue without this conversion
0
dba123Author Commented:
ok, but the calldatetime must be 5 minutes AFTER stop1....this one you show only compares a different of 5 minutes period...so that means it's also pulling cases where stop1 may be 5 minutes greater than calldate time.

I only want ones where calldatetime is 5 minutes after stop1, not the other way around.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

dba123Author Commented:
also this doesn't return any records anyway still:

and datediff(minute, calldatetime, tz.stop1) >= 5
and datediff(hour, calldatetime,tz.stop1) = 0
0
dba123Author Commented:
select      sourceid,
            projname,
            ibob,
            phonenum,
            dbo.GetNumericValue(datepart(hour,calldatetime)) as hrcld,
            calldatetime,
            ac.state,
            tz.zonename,
            tz.stop1,
            dbo.GetNumericValue(datepart(hour,tz.stop1)) as stphr
from history
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 history.calldatetime > getdate()-1  
and zonename <> 'Unknown'
and ibob <> 0
and datediff(minute, calldatetime, tz.stop1) >= 5
and datediff(hour, calldatetime,tz.stop1) = 0
0
TimCotteeHead of Software ServicesCommented:
dba123,

Then you have it the wrong way round, datediff needs interval, lower datetime value, upper datetime value.

If you want calldatetime 5 minutes after stop1 then

datediff(minute,tz.stop1,calldatetime) >=5 ......

Tim
0
dba123Author Commented:
I'm trying something like this now...testing a different way to attempt this..maybe this will work:

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()-2
and datepart(hour, calldatetime) = datepart(hour, tz.stop1)
and (datepart(minute, calldatetime) > datepart(minute, tz.stop1)) > 5
0
dba123Author Commented:
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()-2
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()-2
and datediff(hour, tz.stop1, h.calldatetime) = 0
0
dba123Author Commented:
futhermore why does this:

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 like it should
0
dba123Author Commented:
someone else in aother forum wrote this

I checked this out on dates that I have comparing to the current date, using the function DATEDIFF on SQL Server.My check indicates that the hours value will be cumulative over days. Therefore, each day difference adds 24 hours to your hour total.
0
dba123Author Commented:
the guy also wrote:

Forgot to offer a solution.
If I were doing this, I would cast or convert the time columns to character and compare the subtring of the columns that relates to the hour portion.
0
dba123Author Commented:
so maybe that's why i need to convert this in the first place!
0
dba123Author Commented:
I figured a query out...if you know a better way, I'm all ears

select historyid, 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()-2
and datepart(hour, calldatetime) = datepart(hour, tz.stop1)
and (datepart(minute, calldatetime) - datepart(minute, tz.stop1)) > 5
0
Scott PletcherSenior DBACommented:
So you do not want to find things like this:

calldatetime = 01/01/2006  13:58
stop1          = 01/03/2006  14:01

that is, within 5 minutes, but across an hour bounday?
0
dba123Author Commented:
Scott, yes.  calldatetime must have something like 14:05 and stop1 14:00
0
dba123Author Commented:
I mean calldatetime 14:06 and stop1 14:00 for it to be valid...that is, calldatetime is more than 5 minutes past the same our as stop1
0
Scott PletcherSenior DBACommented:
But what about *across hour boundaries*?  (See example above.)  Your code will not pick those up if you need them.
0
dba123Author Commented:
yes, I do want hour boundaries...so my code won'd do this...correct.  so then how can I take my solution to cover that?
0
Scott PletcherSenior DBACommented:
maybe this:

where calldatetime > getdate() - 2
and datediff(minute, convert(char(8), calldatetime, 112), calldatetime) - datediff(minute, convert(char(8), tz.stop1, 112), tz.stop1) between 6 and 59
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.