T-SQL (SS2K8) :: Difference In Seconds To Round To Minute?
May 20, 2014
I'm trying to get the difference between the two times in minutes with the seconds rounded up or down
As an example the difference in the times:
1900-01-01 09:27:49.000, 1900-01-01 09:32:28.000 is 279 seconds which is 4.65 minutes,
using
select
cast(round(datediff(second, convert(datetime, '1900-01-01 09:27:49.000'), convert(datetime, '1900-01-01 09:32:28.000')) / 60, 2) as numeric(18,2))
I am receiving the value 4.00 instead of 5 which I would like 4.65 to round to. How can I get the difference in seconds to round to the minute ?
View 5 Replies
ADVERTISEMENT
Aug 14, 2015
I have configured an alert like below to track all blocked events in SQL Server across all databases and then kick start a sql job when a blocking happens which inserts data to a table, when there is a blocking in SQL server , i get an email --which is working fine and i am able to track all queries.
but, HOW to get notifications ONLY if BLOCKING IS HAPPENING FOR MORE THAN 30 SECONDS OR 1 MINUTE with out using sp_configure?
---ALERT
USE [msdb]
GO
EXEC msdb.dbo.sp_update_alert @name=N'Blocking Process',
@message_id=0,
@severity=0,
@enabled=1,
[Code] .....
View 4 Replies
View Related
Dec 6, 2007
I found in another forum that if I take the seconds and divide them by 15 then round up and multiply them by 4 I can get this done, but I can't figure out how to work it into my select statement. Anyhelp would be greatly appreciated. dbo.SLPTRANS.TimeSpent is the field I am trying to convert.
SELECT dbo.SLPTRANS.ClientID, SUM(dbo.SLPTRANS.TransValue) AS Expr1, dbo.SLPTRANS.TimeSpent AS Expr2
FROM dbo.SLPTRANS INNER JOIN
dbo.INVOICE ON dbo.SLPTRANS.InvoiceID = dbo.INVOICE.RecordID
GROUP BY dbo.SLPTRANS.ClientID
HAVING (dbo.SLPTRANS.ClientID = 405)
View 4 Replies
View Related
Dec 8, 2007
Hi All,
I want to get the date Difference with Day,hours,minutes,seconds of the given 2 dates.(Say the difference of
12/6/2007 7:00:00 AM, 12/8/2007 8:00:00 AM as 2 days 1:00:00)
Is there any inbuilt function in SSRS is available to implement this. Or any other way to do this.Please help me with this .Thanks in advance.
With Thanks
M.Mahendra
View 2 Replies
View Related
Nov 2, 2015
I have a situation where I have table with over a billion records and needs to be scrubbed. Table does have a field with date time timestamp. I have been deleting rows from the table using the script below which basically provides me delete statements by date for records older than 90 days.
But now on each day row count is over 30 million rows and it takes forever to delete by date and transaction log becomes humongous.
So I would like to scrub it in 5 minute intervals instead of daily for records older than 90 days. Even in 5 minute intervals the record count tends to be around a million. This will keep the delete slice small enough to not a gigantic transaction log.
declare @startdate Datetime
declare @enddate Datetime
set @startdate = getdate()-480
set @enddate = getdate()-90
--set @vStart = select convert(varchar,@startdate, 102)
print @startdate
print @enddate
WHILE (@startdate < @enddate)
BEGIN
print 'delete from vending where DetectedDate < ''' + CONVERT(varchar(10), @startdate, 101) +''''
set @startdate = @startdate+1
END
I am hoping to modify the script above to produce a script with statements like this for a window between last 90 and 120 days:
delete from vending where DetectedDate <'6/15/2015 8:25:00 PM'
go
delete from vending where DetectedDate <'6/15/2015 8:30:00 PM'
go
delete from vending where DetectedDate <'6/15/2015 8:35:00 PM'
go
View 2 Replies
View Related
Sep 23, 2015
In sql we have a built in function
DATEDIFF(second, startdate, enddate)
How can we achieve the same using Log parser query for date time in format (2015-09-22 10:25:30.5220)
Example:
startdate = 2015-09-22 10:25:30.5220.
Enddate = 2015-09-22 10:26:30.5220
After DateDiff I need to get 6 seconds.
View 5 Replies
View Related
Aug 6, 2015
I have a table that I need to do some computations on all the data but first I need to remove the duplicate records and insert the results into a destination table. Here's the example below. My table has 3.1 million rows. I have tried using the DISTINCT and the GROUP BY but both ways to select the data takes about half a minute to run. I'm wondering if there is a way to increase performance. Users are ok with this time since the process runs overnight but improving it won't hurt. I do have a clustered index on these fields but that doesn't seem to improve any.
SELECTDateYear ,
DateMonth ,
Nbr ,
Nbr1 ,
Nbr2 ,
Datafield1 ,
Datafield2,
[code].....
View 7 Replies
View Related
Sep 16, 2014
I have a column called as NDM$ What I want do it round it the nearest value example I am giving below
34.100->34%
39.8->40
35.4->35 some thing like that.
View 6 Replies
View Related
Jul 23, 2014
I have the following problem with ROUND. When doing the calculation for each value by a percentage using round, the sum of the result does not equal the sum of the values for the percentage (also using round).
IF OBJECT_ID('Tempdb..#Redondeo') IS NOT NULL DROP TABLE #Redondeo
Create Table #Redondeo (Orden int Identity(1,1),Valores money)
Insert into #Redondeo
Select 71374.24 Union Select 16455.92
Union Select 56454.20 Union Select 9495.18
[Code] ...
RESULT: 177673.74; 37311.49
View 6 Replies
View Related
Sep 8, 2014
how to round a datetime variable to the nearest second. The solution must NOT just strip off the milliseconds, it needs to round.
Also, the solution should not be too cumbersome because it will be used in a high volume environment.
View 5 Replies
View Related
Jun 3, 2014
I want to calculate total sum of column.
I have a table with many values of column:
declare @time table
(times nvarchar(255))
INSERT INTO @time (times)
Select ('05:01')
UNION ALL
Select ('00:01')
UNION ALL
Select ('170:01')
UNION ALL
Select ('2543:01')
Select * from @time
How can I get seconds value for every row?
05:01 - 301
00:01 - 1
170:01 - 10201
P.s. Finally I use - Select convert(char,dateadd(second,152581,60),108) as sumtime
View 2 Replies
View Related
Apr 23, 2014
Is there a difference in the following: DECLARE @T1 VARCHAR(50);
SET @T1 = (SELECT TOP(1) Col1 FROM MyTable); -- define variable first way
SELECT TOP(1) @t1 = Col1 FROM MyTable; --
define variable second wayI did a quick test and not finding a difference, however I was told that if nothing is returned by the query the result would be different and that the first way is better.
View 8 Replies
View Related
Apr 26, 2014
I've table and data as follow,
declare @tAccount table
(
isLocked bit,
LastLockoutDate datetime,
currentDte dateTime
)
insert into @tAccount values('true','2014-04-26 16:11:25.337',getdate())
Let's say, current data as follow,
isLocked| LastLockoutDate| currentDte
12014-04-26 16:11:25.3372014-04-27 01:45:15.053
How to get different in minutes between currentDate and LastLockoutDate? Mean, currentDate - LastLockoutDate.
View 3 Replies
View Related
May 27, 2014
I have two similar tables in different database and need to make query to select only the data from the first table that is not available in the second table and there is no unique record for each record , but each row is having different records for example:
CREATE TABLE table1(
[PNR] [nvarchar](10) NOT NULL,
[Tkt_Number] [nvarchar](10) NOT NULL,
[DepaCityName] [nvarchar](50) NULL,
[ArriCityCode] [nvarchar](3) NULL,
[Code] ....
Output should be like this
PNR | TKT_NUMBER | DEPACITYNAME | ARRCITYCODE | NAME
fdf44 12669 Sah DXB lamees
View 8 Replies
View Related
Jul 23, 2005
Hi all.If I've got a query which has a field with seconds in it... how will I usethe Convert function to get my field converted to the format: HH:MM:SS ?The field with the seconds in is called: "Diff"Thanks alotRudi
View 2 Replies
View Related
Jan 19, 2015
select datediff(wk, '2015-01-11', '2015-01-19') returns 1 (shouldn't it return 2?)
select datediff(wk, '2015-01-10', '2015-01-19') returns 2
View 5 Replies
View Related
Sep 21, 2007
I have a Stored Procedure that has a query in it and it take 0 second and then a stored procedure that takes 16 seconds. From what I can tell they shoul be the same.
It doesn't recompile when i run the stored procedure, I checked that.
View 8 Replies
View Related
Jan 30, 2006
Anybody noticed that SQL Server rounds up if the value is half waybetween two rounded values, but C#'s Decimal.Round(Decimal,Int32)rounds to nearest even number?[color=blue]>From MSDN: "When d is exactly halfway between two rounded values, the[/color]result is the rounded value that has an even digit in the far rightdecimal position. For example, when rounded to two decimals, the value2.345 becomes 2.34 and the value 2.355 becomes 2.36. This process isknown as rounding toward even, or rounding to nearest."I perform the same calculation sometimes on the web server in C# andsometimes at the database in T-SQL, but want to get the same resultfrom both calculations. Could anybody offer any strategies for dealingwith this?Thanks ~ Matt
View 3 Replies
View Related
Oct 24, 2007
Question is in the subject.
Thanks in advance
-Jamie
View 7 Replies
View Related
Mar 7, 2007
Is it possible expire a report cache after less than one minute? I'm looking for a way to only have a report hit the database once every 10 seconds, no matter how many people are hitting it. Thanks.
View 1 Replies
View Related
Feb 1, 2008
Hi,
Here is a part of result set.
It is of every minute value.
How can I get every other 5-minute average value?
id datetime value
------------------- ----------------------------- --------
0xC00302FD 2008-01-31 18:36:00 0.104
0xC00302FD 2008-01-31 18:37:00 0.104
0xC00302FD 2008-01-31 18:38:00 0.104
0xC00302FD 2008-01-31 18:39:00 0.104
0xC00302FD 2008-01-31 18:40:00 0.104
0xC00302FD 2008-01-31 18:41:00 0.104
0xC00302FD 2008-01-31 18:42:00 0.104
...
...
...
View 1 Replies
View Related
Jan 17, 2002
How do i calculate the Transactions Per Minute (TPM). Do i need to use the Performance Monitor or Profiler. Let me know How do i calcualte.
I would like to have 24,000 inserts in One minute Per data migration. Is 24,000 will be Transaction Per Minute.
Thanks in Advance.
SS
View 1 Replies
View Related
Aug 22, 2007
I need a query that gives me the sum of every rows (time column) with lower 'rownr'
the result:
rownrtimetimesum
1100
21010
31020
41030
51040
61050
71060
81070
current table looks like this:
rownrtime
110
210
310
410
510
610
710
810
and i want the 'timesum' column to be in format hhhh:mm
current format is rownr=int, time=datetime
thx for all help
//Mr
View 14 Replies
View Related
Jan 28, 2008
Hello
Probably a very simple problem, but im stumped. I have a table which gives the start-time and end-time of an employees work day. I want to create a view which contains a line of data for each 5 minute period worked. Please help.
View 3 Replies
View Related
Jan 18, 2005
I would like something I can do inline eg:
select convert(blahdatatype,a.datefield) as smallerdatefield
from
a
where a.datefield is a datetime. If a contains rows like:
datefield
---------------------
01/20/2005 22:17:23
08/23/2001 03:04:15
...
Then the SQL above returns:
smallerdatefield
---------------------
01/20/2005 00:00:00
08/23/2001 00:00:00
...
Is there any non-obnoxious way (eg: without have to result to using datepart a million times) to do this? For instance, Oracle provides a function called Trunc which does it, but I cannot find an SQL Server equivalent. Anyone? TIA!!!
View 9 Replies
View Related
Mar 6, 2012
I have collected perfmon data that is in every 15 seconds. I need to run a query that will only retrun rows that are 5 minutes from the last row starting at a specific date/time.
Here is the current query
Select
DisplayString,
MachineName,
ObjectName,
CounterName,
InstanceName,
[Code] ......
"CounterDateTime" is in every 15 seconds. So starting from '2012-03-02 11:59:00' I need only rows for every 5 minutes after that.
View 14 Replies
View Related
Feb 18, 2008
Hello all,
I have the following very simple SQL Query:
SELECT TOP (100) AutoNumber, Date, ZNT, SAT, RAT, RH
FROM HV_Values
ORDER BY Date ASC
This shows the top 100 entries for a table (where there is a date entry every 5 minutes). Here's five sample records:
1 12/4/2006 4:12:11 PM 67.13 70.50 71.56 8.23
2 12/4/2006 4:17:11 PM 67.13 70.50 71.56 8.33
3 12/4/2006 4:22:11 PM 67.19 70.69 71.69 8.19
4 12/4/2006 4:27:11 PM 67.19 70.63 71.69 8.18
5 12/4/2006 4:32:11 PM 67.19 70.69 71.75 8.05
What I'd like to do with the sample query above is take an AVG for one hour for the values ZNT, SAT, RAT, RH...like I stated the records are every five minutes in the table, I'd like to create 60 minute AVGs.
Any ideas or sample queries I can work with?
Thank you very much! gad1
View 5 Replies
View Related
Jan 31, 2008
hi, i need to synchronize a table from a database to two tables on a different database. I was thinking in a stored procedure to do it and create a job that execute it every minute.
I don't know if this is the right method... maybe it can be a bad decision for the server performance.
Anyone can help me to do that?
View 3 Replies
View Related
Jan 22, 2004
Just wondering if anyone knew if it was possible to create a SQL Server agent job that ran once every 30 seconds. I can't enter a value below 1 minute when editing the job schedule in Enterprise Manager.
Alternatively, I can create duplicate stored procedures and set one to run on the minute and one on the half minute. Thought there might be a better alternative to this though and I'll always have to be remembering to makes changes to both sprocs.
Thanks!
View 3 Replies
View Related
Jun 9, 2015
SQL is generating dump every 1 min with error
EXCEPTION_ACCESS_VIOLATION reading address 0000000000000020 at
Server is running SP2 CU2.
View 1 Replies
View Related
Apr 9, 2015
Just wondering what is the best time to ensure that we only return data when the datetime field is the same when compared between two datetimes within a minute difference.
As in the following should return the data:
'2015-04-09 09:00:20' compared to '2015-04-09 09:00:50'
And the following should not return the data:
'2015-04-09 09:01:20' compared to '2015-04-09 09:00:50'
The problem, is that I'm merging data from three different result sets, which they all have data for every minute, however, the timestamp can be different by seconds or milliseconds.
So, I'm only interested to return the data when the two fields that I'm comparing are equal within a minute. I need to ignore seconds and milliseconds.
View 4 Replies
View Related
Aug 19, 2006
I'm trying to create a query to return Open, Close, Max and Min Price for each 1 minute interval. Source data has two fields - Price, and Datestamp at 5 second intervals.
I can calculate the Max and Min (below) and set the datestamp to the middle of the interval, but get stuck on how to also return the Open and Close price for each interval.
SELECT MAX(price) AS MaxPrice, MIN(price) AS MinPrice,
DATEADD(ss, 30, DATEADD(n,DATEDIFF n, '1/1/2006', DateStamp),'1/1/2006')) AS DateStamp
FROM MasterData
GROUP BY DATEDIFF(n, '1/1/2006',DateStamp)
Any ideas?
thanks in advance.
View 1 Replies
View Related
Jan 28, 2006
Right now the database I am working with is storing time inan Integer data type and is storing the time value in seconds.The application does not allow entering seconds. It acceptsminutes and hours.I have a report where it is doing:SELECT SUM(TIMEENTERED)and the SUM is *blowing* up as the SUM is reachingthe BIGINT range.I can fix the problem by changing all codes to:SELECT SUM(CAST(TIMEENTERED AS BIGINT))But now that I ran into this problem I want to find outif storing the time in seconds using INTEGER datatype is the best solution?I've been searching this newsgroup and other placesthe whole day. I even ran into my own three year oldpost. Three years ago my problem was data migrationrelated and now it is more of performance related thananything else.http://groups.google.com/groups?as_...y=2006&safe=offI could not find this specific topic in SQL books likeSQL for Smarties 2005 by Joe Celko (very good stuff ontemporal topics but nothing specific to my question),or Inside SQL Server 2000.Which data type would be ideal and why?smalldatetime?integer?decimal?float?The type of operations that are being done in the databaseare:1- Entering time in hours on work done on a taskFor the data entry part, the application accepts2.5 as 2 and a half hours and it is storing2.5 * 3600 = 9000 seconds.It also accepts entering 2:30 as 2 hours and30 minutes and again storing 9000 seconds.I even saw a page where you can enter clocktime: I worked from 9:30AM to 12:45PMas an exampleWhen i checked the underlying table(s) I sawthat the ENTEREDTIME is always the durationin seconds. So the data entry can either be2.5 hours where ENTEREDTIME = 9000 secondsor9:00AM to 11:30AMwhere STARTDATE is today's date for examplestored as 1/27/2005 09:00AMand where ENTEREDTIME = 9000 seconds2- All kinds of reports showing total time in hoursfor example: Project1 = 18.5 hoursThe code in the SP are all like:SUM(ENTEREDTIME) / CAST(3600 AS DECIMAL(6,2))AS TOTALTIME3- I am sure a lot of other arithmetic calculations arebeing done with this ENTEREDTIME field.What would be the best way to store hours/minutesbased on how we are using Time in the database?Either I will stick with Integer but store in minutestime instead of calculating in seconds and most likelyupdate all the SUM(ENTEREDTIME) toSUM(CAST(ENTEREDTIME AS BIGINT))or I will switch to storing in decimal/float andmaybe avoid doing :SUM(ENTEREDTIME) / CAST(3600 AS DECIMAL(6,2))AS TOTALTIMEsince the ENTEREDTIME would already be storedin hours time.or I will use DATETIME since in the cases ofI worked from 9:00AM to 11:30AMI have to have a separate column to store the date also.I am a little confused I am hoping I will get some helpfrom you and maybe if I can't find the best solution, atleast eliminate the NOT so good ones I am thinking of.Thank you
View 1 Replies
View Related
