Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

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.
0
dba123
Asked:
dba123
  • 13
  • 3
  • 2
  • +1
1 Solution
 
TimCotteeCommented:
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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
 
TimCotteeCommented:
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 13
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now