SQL Server 2012 :: Turning Datediff Minutes Into Dd:hh:mm
Jul 7, 2015
I am going put in the whole query but my question is on changing a datediff minutes amount into DD:HH:MM.
Query is:
select c.APPLICANT_ID as [Applicant ID], aetc.EVENT_TYPE as [Event Type],
cast(aetr.CREATE_DATE as date) as [Registration Date],
cast(aetc.CREATE_DATE as date) as [C Creation Date],
datediff(mi,cast(aetr.CREATE_DATE as datetime),cast(aetc.CREATE_DATE as datetime)) as [time diff],
[Code] .....
I want this as dd:hh:mm rather than just minutes.
Currently this field is just minutes so a figure such as 20, depending on the time difference between the 2 dates.
I have tried the convert statement convert (char(5) ...........,108) within this but that's not working and I have used floor before to do this type of thing but not sure where that should go.
I 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)
I need to create a stored procedure to select records that cal_callin_tm (this is a datetime field in table) is less than 10 minutes of the current system datetime. I want to update cal_callin_tm field with current system time and pass back the cal_assign_off_p field to my VS 2008/C# windows form. I'm new at SQL code, I know the basic stuff but this requires more knowledge than I have. I need from you experts the best practice to accomplish coding the SQL. I have some junk code below I have started with, help me out.
Thanks a bunch,
CREATE PROCEDURE SP_DpOffCallIn
(
@offname varchar(40) = null OUTPUT,
)
AS
SELECT * from dpcalldtl
where cal_callstat = 'ON SCENE' and (datediff(minute,0,cal_callin_tm) < datediff(minute, 0, currentdate)
I needed to create a stored procedure to lock a user who makes 3 incorect entries of his password. I did it successfully. Now the problem what i have is that i want to lock the user  only if he makes the 3 incorrect entries within 30 minutes.
I created a field named "FirstEntryTime" of type datetime  that saves the date of the first incorrect entry.  I tried to make an if statement:
if (@timesOfEntry <=2 AND DATEDIFF(MINUTE, firstEntryTime,GETDATE()) <= 30) Begin  Update myTable set ...  End
I 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)
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.
So I am trying to work out the difference between today's date (GETDATE()) and a Target Date in a specific table (targetdate)
When I use the DATEDIFF function it is including non working days in the calculation (weekends and bank holidays). Although our date calandar table provided to us from a third party supplier will tell you the weekends, it does not tell you the bank holidays.
Luckily there is another table in the database called - ih_non_work_days.
The format of the date is "2014-12-25 00:00:00.000" for example in that table.
How do I using my "targetdate" and today's date calculate in days the differance - excluding the dates that exist in the ih_non_work_days database?
So for now my basic script looks like -
SELECT com.comm_reference AS 'Referance' ,com.current_task_target_date AS 'TargetDate' , DATEDIFF(D,com.current_task_target_date,GETDATE()) AS 'Incorrect Date Calculation' FROM [dbo].[em_communication] as com
During investigation problem with disk i found some issue, that every 2 minutes is writing on disk , it looks like to mdf file of database, but in almost 6-8milions Bytes / sec , it is about 1-2 sec , but every 2minutes... this is normal behaviour ? this is synchonyous writing to disk from memory.
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'))
Hello, I€™m loading the Fact table of more then 8 million records. The SQL Server Database is taking hell lots of time to get this insertions and updations. Can any one guide me on turning the SQL Server 2005 Database.
Subject says it all, really. I want to start using Token Replacement,but do I break anything by enabling it? Do jobs that don't use tokensrequire any changes? I saw somewhere that it can't be turned off, soI'm paranoid about enabling it. Anything that I should be aware of?Many thanks.
do you have a general rule of thumb for breaking a complex query into temp tables? For someone who is not a sql specialist, a query with more than a few table joins can be complex. So a query with 10+ table joins can be overwhelming for someone who is not a sql specialist.
One strategy is to break a problem into pieces so to speak by grouping together closely related tables into temp tables and then joining those temp tables together. This simplifies complex SQL and although not as performant as one big query it's much easier to understand. So do you have a general rule of thumb as far as a threshold for the number of joins you include in a query before you break the query into temp tables?
This is a sql question, so I'm hoping someone can give me some direction. I need to query a table and return all rows where the individual is >= 18 years of age. It should involve simply subtracting the date of birth from the current date. I thought the answer would be so simple: select * from table where datediff(yy,dob,getdate()) >= 18 Unfortunately, the results are inconsistent. The DATEDIFF function, it appears, will not show correct results down to the month/day level. I tried posting this on a sql server forum, but the recommendations I received involved coding some gigantic function. It seems that there ought to be a simple 'WHERE' clause which could solve this. If someone knows of a silver bullet, I would surely appreciate your advice. Thank you.
We have an application that has about 100 users at a time. Roughly once a day, we experience a complete slowdown on the server. All users notice it. The network seems fine because I can ping the server. Also, I can attach to drives on the server quite fast so I don`t think it`s server resources. When I manage to get in and do an sp_who, certain processes are blocking others. Talking to the users who were blocking, they were not doing anything out of the ordinary - one was even doing just a select. The error log is full of 17824 and 1608 errors. Is there some configuration setting that I should change? This is getting serious!
Sorry i think i may have posted this in the incorrect forum before - if i have done it again here can someone tell me where i should post this please, thanks:
Hi, we are having problems with a server Intel RZeon 3ghz, 3gb ram running 2003 service pack 2 with a 70gb drive and and 400 gb drive all with adequate free space. There are 6 hard disks in total and i assume operating at least RAID 5. We have SQL2000 server with a few standard sized databases and a connection to one other server.
A few months ago the back up of SQL server databases started taking 4- 5 hours when before it took 20 minutes. We had actually lost one of our disks in the RAID array and it before this was spotted by our engineers we reindexed the sql databases and defragged both 70gbC: and D: 400gb drives hoping to correct this slow down. Unfortunately the new disk had not been correctly seated and this was why it was taking 4-5 hours. After fixing the disk the backups took 12 minutes again but then started taking 2-3 hours after a few days.
The reindex/defrag did seem to improve the speed of the backups to 12 minutes (from 20 minutes) when the backup did function correctly (also the sql databases' performance improved). However the backups only take 12 minutes after a server reboot - this can last from only 2, up to 5 backups(days) in a row before a slow down to 2-3 hours and again only a reboot will sort out this problem.
NB this intermittent slowdown only occurred after the disk failure.
We have tried monitoring SQL server and can find no CPU/RAM intensive clashes or long running jobs interferring with the back up. Does anyone know what might be going on here? and if there are any server monitoring tools that may help us discover what is causing this problem ?
I'm using the following SQL String in an ASP script with SQl Server:
SELECT time, DATEDIFF(minute, time, '18:00:00') AS difference FROM appointments
I have two appointments in the database - one with a time of 11:00 and one with a time of 14:00.
When I run this SQL query, 'difference' comes out exactly 48 hours more than it should do. So for the first appointment, it says that the difference is 3300 minutes and for the second it is 3120 minutes.
Any idea why this is happening? Is it something to do with the fact that I'm not using dates in the datetime format?
Can anyone help me? My brand new installation of SQL Server 6.5 running on Windows 2000 suddenly stops working for about 6 minutes, in this periods no one can use SQL 6.5 services because it stops responding. My logs is show below:
2002/04/08 15:50:22.60 ods Error : 17832, Severity: 18, State: 0 2002/04/08 15:50:22.60 ods Unable to read login packet(s). 2002/04/08 15:50:24.50 ods Error : 17832, Severity: 18, State: 0 2002/04/08 15:50:24.50 ods Unable to read login packet(s). 2002/04/08 15:50:26.79 ods Error : 17832, Severity: 18, State: 0 2002/04/08 15:50:26.79 ods Unable to read login packet(s). 2002/04/09 07:10:21.48 ods Error : 17832, Severity: 18, State: 0 2002/04/09 07:10:21.48 ods Unable to read login packet(s). 2002/04/09 07:53:33.76 ods Error : 17824, Severity: 10, State: 0 2002/04/09 07:53:33.76 ods Unable to write to ListenOn connection '.pipesqlquery', loginname 'sa', hostname '2M_ROSANNA'. 2002/04/09 07:53:33.76 ods OS Error : 232, 2002/04/09 07:53:33.78 spid75 Error : 1608, Severity: 21, State: 2 2002/04/09 07:53:33.78 spid75 A network error was encountered while sending results to the front end. Check the SQL Server errorlog for more information. 2002/04/09 07:53:41.10 ods Error : 17824, Severity: 10, State: 0 2002/04/09 07:53:41.10 ods Unable to write to ListenOn connection '.pipesqlquery', loginname 'sa', hostname 'CE_SANDRA'. 2002/04/09 07:53:41.10 ods OS Error : 232, 2002/04/09 07:53:41.10 spid80 Error : 1608, Severity: 21, State: 2 2002/04/09 07:53:41.10 spid80 A network error was encountered while sending results to the front end. Check the SQL Server errorlog for more information. 2002/04/09 07:55:42.20 ods Error : 17824, Severity: 10, State: 0 2002/04/09 07:55:42.20 ods Unable to write to ListenOn connection '.pipesqlquery', loginname 'sa', hostname 'CE_SANDRA'. 2002/04/09 07:55:42.20 ods OS Error : 232, 2002/04/09 07:55:42.20 spid53 Error : 1608, Severity: 21, State: 2 2002/04/09 07:55:42.20 spid53 A network error was encountered while sending results to the front end. Check the SQL Server errorlog for more information. 2002/04/09 07:57:09.40 ods Error : 17824, Severity: 10, State: 0 2002/04/09 07:57:09.40 ods Unable to write to ListenOn connection '.pipesqlquery', loginname 'sa', hostname 'FE_FERTILIDAD'. 2002/04/09 07:57:09.40 ods OS Error : 232, 2002/04/09 07:57:09.40 spid91 Error : 1608, Severity: 21, State: 2 2002/04/09 07:57:09.40 spid91 A network error was encountered while sending results to the front end. Check the SQL Server errorlog for more information. 2002/04/09 07:57:09.40 ods Error : 17824, Severity: 10, State: 0 2002/04/09 07:57:09.40 ods Unable to write to ListenOn connection '.pipesqlquery', loginname 'sa', hostname 'SF_ROSA'. 2002/04/09 07:57:09.40 ods OS Error : 232, 2002/04/09 07:57:09.40 spid11 Error : 1608, Severity: 21, State: 2 2002/04/09 07:57:09.40 spid11 A network error was encountered while sending results to the front end. Check the SQL Server errorlog for more information. 2002/04/09 07:57:36.04 ods Error : 17824, Severity: 10, State: 0 2002/04/09 07:57:36.04 ods Unable to write to ListenOn connection '.pipesqlquery', loginname 'sa', hostname 'CE_MILAGROS'. 2002/04/09 07:57:36.04 ods OS Error : 232, 2002/04/09 07:57:36.04 spid60 Error : 1608, Severity: 21, State: 2 2002/04/09 07:57:36.04 spid60 A network error was encountered while sending results to the front end. Check the SQL Server errorlog for more information.
The problems begin on 2002/04/09 at 7:53am. and it respond again at 7:57, there's no need to restart the service.
How can i calculate datediff (in minutes) between two consecutive rows without using CTE & Temp tables?
I was using this successfully, but i need something without CTE and temp tables (both of them are not supported in the tool i am trying to generate a report using custom query).
WITH CTE AS (SELECT ROW_NUMBER() OVER (ORDER BY Id desc) AS RowNo, * FROM MyTable) SELECT t1.*, ISNULL(DATEDIFF(mi, t2.CreateDate, t1.CreateDate), 0) AS Duration FROM CTE t1 LEFT JOIN CTE t2 ON t1.RowNo = t2.RowNo - 1 ORDER BY t1.Id desc
I have two servers, one production server and one backup server which have transactional replication with a pull subscription.
When I configure replication, it works fine during our test weekends testing production load. After tests, replication looks fine for a random number of days. Then, all of a sudden, an error message is displayed on one of the agents: "The agent is suspect, no response within 10 minutes." This has happened a number o times. If I remove replication and configures it again, it always works. Sometimes it works by just updating one of the tables and the error message disappears. The last time (today) that did not work. Updating the database did not replicate and the error message remained.
Has anyone experienced this same problem and has a god solution. One thing that is common is that the error message appears after long times of inactivity on the servers, or perhaps after a restart but that I am not sure about.
Question 1: How can I prevent this error message?
Question 2: Are there any special things to think about when I need to restart the servers and replication is configured, e.g. after installing updates from Windows Update.
I would be very grateful for any answers regarding this.
i've tried using the SELECT SQL sentence on PPC with UNION and INNER JOIN in combination with DATEDIFF function which works fine on ordinary SQL but obviously does not work on compact edition. Is there some workaround?
SQL sentence that works:
Code Snippet
SELECT * FROM ( SELECT 'O' Type, O.Name , OC.Name Contact, Birthday FROM OutletContact OC INNER JOIN Outlet O ON OC.OutletID=O.OutletID UNION SELECT 'W' Type, W.Name,WC.Name Contact, Birthday FROM WholesalerContact WC INNER JOIN Wholesaler W ON W.WholesalerID=WC.WholesalerID ) S WHERE Birthday IS NOT NULL AND (DATEDIFF(day,GETDATE(),Birthday) BETWEEN 0 AND 14)
I am trying to return a 0 when there is a NULL as a result, I thought that Sum(ISNULL(Payment,0) would do the trick, I was wrong...any ideas: MonthlyCredit = (Select Sum(ISNULL(Payment,0)) from transaction as t Where AccountNumber = @AccountNumber and Month(t.PayDate) = @Month )
Hi is it possible to run a certain SQL statement agaisnt SQL Server and ask it not to fire any triggers? Or is would it be better to disable the trigger and then reable it after ward? If so how? Thanks Ed
I have a device application that simply needs to upload data to a server. The preferred DB server is Oracle but I've made it work using RDA and SQL Server. The problem I'm having is that it just needs to upload data, whichh I send using the RDA.Push() method. The data arrives just fine, the first time. With every subsequent upload all of the previous data is deleted fromt he server. Apparently RDA is tracking the deletion of the previously uploaded data locally and on the next .Push deleting that data from the server.
My question is: Is it possible to prevent RDA from deleting data on SQL Server? I attempted to delete the rows from the __sysDeletedRows/__sysRowTrack tables but got a "Data is read only" error.
--Environment: SQL Server 2000 I am doing following query for who is reaching of Age 65/75 in May 2008 but Member_DOB's or Spouse_DOB's showing different month. Month should show '05' because I want to see results who is reaching of age 65/75 in May 2008 only. Please help in this regard. --Query: Select m.empid, m.dob "Member_DOB", round(datediff(dd,m.dob,'05/30/2008')/365.25,1) Member_Age, d.dob "Spouse_DOB", round(datediff(dd,d.dob,'05/30/2008')/365.25,1) Spouse_Age from member m left outer join (SELECT * FROM depend where depcode = 'S' and activestatus = 1)d on m.empid = d.empid where (datepart(yy,m.dob) in (1933,1943) or datepart(yy,d.dob) in (1933,1943)) and round(datediff(dd,m.dob,'05/30/2008')/365.25,1) in (65,75) or round(datediff(dd,d.dob,'05/30/2008')/365.25,1) in (65,75)
Does anyone know if there is an easy way to turn off the "Select All" option from appearing on reports with multi-selects? I am going to have a hard time getting the development staff to update all of our reports AGAIN after making them conform to SP1.
Please let me know if there is a way before I install SP2.
I need to turn off validation and I've seen some threads saying this is not possible but my situation has a twist.
A customer needs the package to connect to different modem dialup connections to connect to different servers (they use dialup for security reasons). We have written two VB script tasks at the beginning and end of a loop, with data flows in between. Before the loop the dialup connection info is read into a recordset along with Data Source connection information. The first script uses this information to dialup and the last script hangs up the connection. The problem is the package tries to validate the data connections and the package has not dialed up yet, so it fails.
We managed to confirm it works in a test environment by putting a break in the first script, manually VPNing into the test network (to allow validation of the data flow to work), and then manually disconnecting from VPN during the break. The script dials in and pumps the data. But this won't be an option in production.
So if anyone has figured out a way to turn off validation, great. Otherwise, any ideas to make this work? I was thinking about setting up a dummy connection that would be connected outside the package before running just for validation (and then the script would disconnect to begin, but I would prefer to handle all of this within SSIS.
Any help? While I see the point of validation it's a bummer that MSFT didn't put this in the hands of the user.
