Date - 15 Minutes
May 11, 2008how can i get my date minus 15 minutes in sql?
is there a dateadd function?
ADVERTISEMENT
Reporting Services :: Formatting A Date Field To Just Show Date Not Minutes?
Jul 6, 2015A date field in a report returns the date value as:
2015-07-01 13:30:27.000
Is there any way I can script this to appear as:
01 July 2015 (or 01-07-2015 or 01/07/2015)Â - basically to cut out the hours, minutes and seconds?
The best I have managed is: CONVERT (varchar(17),DATE,113) AS Date1 but this still leaves me with:
01 July 2015 13:30
How To Subtract Minutes From The Date?
Oct 18, 2007I want to fetch all the records which are 2 minutes older
How can I do it?
Getting Date Difference Along With Day,hours,minutes,seconds
Dec 8, 2007Hi 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
SQL Server 2012 :: How To Add Minutes To A Date Within Working Hours Only
Sep 4, 2014I need to be able to add minutes to a datetime value, which only cover working hours.
I have a holiday table as below:
Examples: (dd/MM/yyyy hh:mm)
Date..........Description
01/01/2015 New Years Day
26/12/2014 Boxing Day
25/12/2014 Christmas Day
25/08/2014 August Bank Holiday
Our Business hours are 08:00-18:00 Mon-Fri (unless the day is in the holiday table)
Start Date............Minute to Add......Expected outcome
01/09/2014 10:00........30...................01/09/2014 10:30
01/09/2014 17:00........65...................02/09/2014 08:05
29/08/2014 17:00........65...................01/09/2014 08:05
22/08/2014 17:00........65...................26/08/2014 08:05
31/08/2014 02:30........65...................01/08/2014 09:05
01/09/2014 19:00........65...................02/08/2014 09:05
01/09/2014 10:00........3005...............08/09/2014 10:05
22/08/2014 17:00........3005...............01/09/2014 17:05
I have tried to create a function to do this (fn_pp_AddMinutesWithinWorkingHours(@StartDate,@Minutes)) but I am unable to come up with a solution which factors in everything correctly.
Format An Date Diff Expression To Hours And Minutes
Apr 4, 2008Hello,
I have this expression DATEDIFF(HOUR, startdate,enddate) which only shows the hours. I need to show the hours and minutes too , exp. 9.17. Any way to convert the expression to do this. The startdate and enddate fields are mm/dd/yyy hh:mms. I am using report builder.
Data Access :: Date Formatting (Convert Minutes Into HH:MM Format)
Jun 20, 2015Please find below my query and result , how to display [Total Service Time ] in HH:Min format (Currently values in minutes)
Query:Â
SELECT Â DISTINCT Â dbo.sectn_dept.sectn_sc AS Customer,
MONTH(dbo.incident.date_logged) AS Month_Number, DATENAME(month,
dbo.incident.date_logged) AS Month, YEAR(dbo.incident.date_logged) AS Year,Â
dbo.incident.incident_ref PM_ref,
dbo.product.product_n "Product",
[Code] ....
 Â
Result:
Need to Display [Total Service Time] in below Format:
But Some values are repeating ....
T-SQL (SS2K8) :: Display Row As 2 Days Ago / 1 Hours 34 Minutes Ago / 11 Minutes Ago
Apr 21, 2015My table as data as follow,
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_Data]') AND type in (N'U'))
DROP TABLE [dbo].[table_Data]
GO
/****** Object: Table [dbo].[table_Data] Script Date: 04/21/2015 22:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_Data]') AND type in (N'U'))
[code].....
How To Re-set A Job To Run 10 Minutes Later
Aug 12, 2005Hi,
I have a job where the first step starts and checks for a condition. If its not true, I want it to reset itself and start again in 10 minutes. I'm using sp_stop_job and sp_update_jobschedule and, initially, it looks like it works. But since it's a Daily job, the 'Next Run Date' increments to the following day. Even though I'm using sp_update_jobschedule to keep the active_start_date as the same day, it still increments. I've tried updating sysjobschedules directly, but get the same results.
Any thoughts much appreciated! Here's my code:
USE msdb
--This is the part that goes in the job step
--and increments the next_run_time if the condition is true.
If
(Select count('x') from mytable (NoLock)
Where PublicationDate > getdate()) < 1
BEGIN
Declare @ActiveStartDate int
Declare @ActiveStartTime int
Select @ActiveStartDate = active_start_date from msdb.dbo.sysjobschedules (NoLock)
Where schedule_id = 61
Select @ActiveStartTime = active_start_time from msdb.dbo.sysjobschedules (NoLock)
Where schedule_id = 61
EXEC msdb.dbo.sp_stop_job @job_name = 'Owners'
Select @ActiveStartTime = @ActiveStartTime + 1000
EXEC sp_update_jobschedule @job_id = '46C074D4-908D-46AE-8B0C-A23E3AD4A4F6',
@name = 'Daily',
@active_start_date = @ActiveStartDate,
@active_start_time = @ActiveStartTime
End
Min/max Of X Minutes
Mar 27, 2006I am trying to develop a sql statement that will create a recordset of the min (or max) values in x minute increments over a period of time.
e.g. over a period of 7 days, I have data that was collected in 1 minute intervals. I need to know the min (or max) value in each 10 minute interval over that same period of time.
Is there an efficient way of doing this?
Group On Minutes
Mar 4, 2002Hi,
I need to write a query whioch give me count on
0-15 minutes and then from 0-720 minutes.
I don't know how to group it.
Any help appreciated.
TIA
Datetime To Minutes?
Aug 3, 2004hey all, i need to find the ratio of difference in 2 datetime variables and the difference of another 2 datetime vars. I figured the best way to do it is to convert the difference in both numerator and denominator to number of minutes.
can anyone help ??
CURRENT_TIMESTAMP - 5 Minutes
Sep 25, 2006:shocked: hi,
I want to use CURRENT_TIMESTAMP - 5 minutes in a select and where clause.
I have tried using
CURRENT_TIMESTAMP - 0.004 AS [Time_-6]
But this is not a round off to a whole minute
Also tried
CURRENT_TIMESTAMP, dateadd(minute, datediff(minute, 0, CURRENT_TIMESTAMP) / 5 * 5, 0)
But this will not do wholes seconds e.g
CURRENT_TIMESTAMP = 10.03.33
CURRENT_TIMESTAMP, dateadd(minute, datediff(minute, 0, CURRENT_TIMESTAMP) / 5 * 5, 0) = 10.00.00
Can anyone help??
MS SQL Performance From 10 To 3 Minutes
Sep 27, 2006Hello all !
I am runing from .NET application an SQL Query
it normally return the rows in 10 seconds
but time to time the application turn 2 or 3 minutes and nearlly crash (or crash)
with exactly the same datas in database
what can be the reasons ?
thank you
Got Seconds But Need Minutes
Apr 16, 2004I have a result that comes out in number of seconds, but need to see it converted to minutes and hours and seconds. Is there a convert function that would do this?
Thanks,
Dan
Group By Every 15 Minutes?
Sep 20, 2013I have this script that runs on a LOGGING database and find hourly requests for a particular firm.
The date format is "2013-08-19 13:44:50.177"
How can I group it by every 15 mins?
select
LocalDate [date],
LocalHour [Hour],
count(*) [requests],
avg(web_request_duration) [avg response time],
min(web_request_duration) [min response time],
max(web_request_duration) [max response time]
[Code] ....
Set Time Up To Minutes
Oct 28, 2014I am using below query to get the today date and time(2 hrs more than actual time)
select dateadd(HOUR, 2, getdate()) as time_added
the result of above query is "2014-10-28 13:19:09.343" but I want time up hours like shown below
"2014-10-28 13:00"
All Records Within X Minutes Of Each Other
Aug 30, 2005Consider a table that holds Internet browsing history for users/machines,date/timed to the minute. The object is to tag all times that are separatedby previous and subsequent times by x number of minutes or less (it couldvary, and wouldn't necessarily be a convenient round number). This willenable reporting "active time" for users (a dubious inference, but hey).There are a lot of derivative ways of seeing this information that might begood to get to. What's the fist and last of these sets of times? Whatpercentage of a given period is spanned by active times, and not? What isthe average duration of such periods? What is the average interval betweenweb hits during such periods? During other times?Blah, blah. The basic problem is my principal problem. I don't have muchexperience with cursors, but from what I understand it would be very goodindeed to spare them, given the number of records I anticipate workingwith.I'd be glad of any pointers.--Scott
View 29 Replies View RelatedThe DB Freezes For 5 Or 10 Minutes
Oct 9, 2007 Hi,
the SQL DB freezes and no one can access the DB for 5 or 10 minutes. Even select queries don t execute, nothing is displayed.
Until we kill the process id that s blocking in the sql activity monitor, only then the DB is released and people can work again.
What does it mean that no query executes until we kill the processes ID? what could it be?
Also, recently we created indexes and ran tuning adviser, is it possible that the creation of indexes cause the freeze of a DB? is that possible?
Thanks a lot for your help
Cannot Connect Again After 15 Minutes
Nov 19, 2006Hi,
I have SQL 2005 full version installed a remote server and when I start my computer I can connect to the database from SQL Managment Studio and a program I'm making. After about 15 minutes I find that I cannot connect using both programs, but if I enter the servers IP address using Managment Studio I can connect (this does not happen with the program I'm making). I have to log of my computer before I can connect again.
Anyone having the same problem or how to fix it??
Thanks
PQSIK
Execute Trigger Every Minutes
Feb 13, 2008I want to create a Trigger that will run every 5 minutes.
This trigger would actually run a Stored Procedure named say "SP_SetRooms".
This SP will in turn run and andupdate the rooms table.
Would anyone know how to help me get started on creating a Trigger with the info I've provided?
Thank you,
Dharmendra parihar
SqlCacheDependency Works Just For 2-3 Minutes
Jan 31, 2006I've the following simple code bound to a button on a Web page
string time = (string)Cache["KEY"];
if (time == null)
{
SqlConnection sqlConnection = new SqlConnection(@"Server=BIZYUSUFSQL2005;Database=Deneme;User Id=sa;Password=;");
SqlCommand command = new SqlCommand(@"select KOLON1 from dbo.CACHE", sqlConnection);
sqlConnection.Open();
SqlCacheDependency dependency = new SqlCacheDependency(command);
time = System.DateTime.Now.ToString();
Cache.Insert("KEY", time, dependency);
command.ExecuteNonQuery();
sqlConnection.Close();
}
return time;
This code has to return the time value from cache. And when a record is inserted into the CACHE table, the cache item has to be invalidated and the new time value has to be returned.
The code works properly for 2-3 minutes. But when there is no activity for 5 minutes, the cache invalidation does not work anymore.
Replicate Every 15 Minutes: Insane?
Jan 15, 2008The company I work for has no experience with replication, and neither have I.
Now I recieved a functional specifications document and apparently what they want is use replication to maintain a copy of a production database on the data warehouse server.
The database is 70GB, and they want to do it every 15 minutes (so their reports will contain up-to-date data).
As I said, I know nothing about replication, but to me, this sounds like madness. I fear that this will will (at least) have a negative impact on the performance of the production database.
So is it possible? Does replication have a big impact on the database or is it hardly noticable? I expect about 500 new records every 15 minutes; production database and data warehouse are on different servers; both are SQL2005.
8000 Records In Over 12 Minutes?
May 28, 2014I have the following SQL select query:
Code:
SELECT
inbound_emails.inbound_email_id,
inbound_emails.campaign_id,
campaign_header.campaign_name,
customers.customer_name,
inbound_emails.date_received,
[Code] ....
Without any indexes (other than PK's) this query would take 1min 21 secs to return all 8500 records.
I wasn't satisfied with that so I added indexes to the tables.
Indexes are;
inbound_emails:
PK: inbound_email_id
1. campaign_id (non-unique, non-clustered)
2. email_from (non-unique, non-clustered)
3. email_to (non-unique, non-clustered)
campaign_header:
PK: campaign_id
1. campaign_name (unique, non-clustered)
customers:
PK: customer_id
1. customer_name (unique, non-clustered)
contracts:
PK: contract_id
1. contract_name (unique, non-clustered)
2. customer_id (non-unique, non-clustered)
Have I made a hash of the indexes? Or is the query statement badly written? What is a reasonable amount of time to expect to retrieve 8500 records?
I think email_from and email_to on inbound_emails is the main culprit, but I don't understand why?
Days Hours Minutes
Apr 18, 2008I have two field and I need to find the sum I guess of them. One is called the clm_dout (process date) and the other one is clm_rcvd (received Date). These are both date and time fields and it looks like this 2006-03-17 00:00:00.000. I need to create a formula that wiil i guess give me the difference and create it into hour and minutes because I an trying to create a turn around report. this is what I had...
TimeValue({clm_doubt}-{clm_rcvd})
This is not working for me. Can someone give me a suggestion please.
Thanks!
Convert Seconds To Minutes
Oct 28, 2005I need to convert 300.876 to 5:00.876 in classic asp. Can anyone help?
View 1 Replies View RelatedCalculate Hours And Minutes
Nov 6, 2006Hi
Can anyone help me convert a number to give the result in hours and minutes? For example 195 as 3:15 or 210 as 3:30. We are trying to create a report showing hours and minutes worked without having to export to Excel.
I've had a look around the net and this seems to be quite a difficult function in SQL Server.
Any guidance much appreciated.
Datediff Function With Hrs And Minutes
Feb 9, 2007I have two date columns one is sent_date and other is approved_date
my requirment is to find the difference between the two dates
which can be minutes/hrs/days.
using datediff function iam able to get it in minusts or hrs but my
output should be of the format hh:mm
23:10 (ie 23 hrs and 10 min) or say
48:00 (for 2 days)
sample date
sent_date approved_date
2/28/06 11:06 2/28/06 11:39
2/2/06 17:42 2/2/06 18:03
2/8/06 16:55 2/8/06 17:38
1/27/06 17:00 1/27/06 17:54
1/26/06 12:08 1/26/06 12:09
2/28/06 15:46 2/28/06 16:26
1/23/06 10:01 1/23/06 10:43
1/26/06 13:46 1/26/06 13:59
1/13/06 13:51 1/13/06 14:47
Hours And Minutes Question
Jun 19, 2007Hi guys,
how to get the Hours and Minutes ago?
I have a column name "DateCreated" which is a DateTime type. I need to get the Hours and Minutes ago
Thank you.
Data Grouped By 20 Minutes?
Nov 28, 2005Hi,I want to get the count of rows per each 20 minutes, is that possible?Is there a date function or any other function that I can use in Groupby clause, which will group the data of every 20 minutes and give methe count?Thank you.Vidya
View 3 Replies View RelatedConvert Minutes To Hours
Feb 24, 2006hi,
I want to convert minutes to hours. for example field_minutes=130minutes to 2:10 hours...
select field_minutes from table---> how can I do?
Please Help- How To Add For Evry Row 15 Minutes With Condition
Apr 29, 2008please need help
i need to create view_circuit like this
for evry row add 15 minutes with condition by station field
Table before
start time@=14:30
ORDER BY 6,9,15
point_collection employee station seated
-----------------------------------------------------------------------------------
start time@=14:30 aaaa 6 station-6
xxxx 9 station-9
nnnnn 9 station-9
cccc 15 station-15
dddd 15 station-15
yyyy 15 station-15
...............................
.........................
Table After
point_collection employee station seated
-----------------------------------------------------------------------------------
14:30 aaaa 6 station-6
14:35 xxxx 9 station-9
14:35 nnnnn 9 station-9
14:40 cccc 15 station-9
14:40 dddd 15 station-9
14:40 yyyy 15 station-9
..................
...................
........
tnx for the help
Predicting A Continuous Value (run Minutes)
Oct 23, 2007Hello,
I have a question regarding whether or not Data Mining can be utilized in a specific problem I have to solve.
Situation: I€™m going to simplify the problem by explaining it in terms of a €œpizza manufacturer€?. Suppose I wanted to predict the run minutes + downtime minutes (I use these to get an hourly rate: Pizzas/(run hrs + delay hrs) = Pizzas per hour) by looking at a set of input properties.
My properties could be something like the following:
# of Toppings
# of Special Pricing Stickers
Cardboard Box Indicator
Case Indicator (0 represents auto-casing, 1 represents putting in case by hand)
Machine Type (0 or 1€¦ 0 represents an older €“slower machine, 1 is newer)
Quantity of Run
(there could be up to 15 other properties that may or may not impact our rate)
Measured Values:
Run Minutes
Delay (down) minutes
Steps I€™ve Done So Far:
I€™ve created a couple different data mining models for this as I was unsure which one(s) to use. I checked the lift chart while feeding back in the original data set and my scatter plot appeared fairly inaccurate.
I've attempted to use Excel to create a linear regression, however my r squared value was always around .30. I decided to try to use SQL Server Data Mining to see if it could be something to help predict our accuracy better than a linear formula.
I've played with a couple different algorithms in Data Mining, and it appeared that none of them did exceptionally well with prediction. I even checked the lift chart using the same table as I used to train the model.
What algorithm(s) might work the best?
Can I reasonably expect a prediction within a fairly strict tolerance (I'm guessing the answer to this is: "yes, if your source data represents a consistent pattern")?
How can I best utilize Data Mining to give an answer like "historically, your run rate has been between these 2 values with a probability of X". I'm thinking I can utilize the predictprobability and stdev to some extent.
Any suggestions would be greatly appreciated.
If anyone needs further clarification, please let me know.
Thank you.
Regards,
Dan