declare @StartDate datetime, @EndDate datetime select @StartDate=”10/01/2012 08:40:18.000″,@EndDate=”10/04/2012 09:52:48.000″ select convert(varchar(5),DateDiff(s, @startDate, @EndDate)/3600)+’:’+convert(varchar(5),DateDiff(s, @startDate, @EndDate)%3600/60)+’:’+convert(varchar(5),(DateDiff(s, @startDate, @EndDate)%60)) as [hh:mm:ss] This query will helpful to you.
I’m not sure what you think this ORDER BY is accomplishing? Even if you do put ORDER BY in the view in a legal way (e.g. by adding a TOP clause), if you just select from the view, e.g. SELECT * FROM dbo.TopUsersTest; without an ORDER BY clause, SQL Server is free to return the … Read more
SQL Server 2008 and up In SQL Server 2008 and up, of course the fastest way is Convert(date, @date). This can be cast back to a datetime or datetime2 if necessary. What Is Really Best In SQL Server 2005 and Older? I’ve seen inconsistent claims about what’s fastest for truncating the time from a date … Read more
So let’s say you have a stored procedure in tempdb: USE tempdb; GO CREATE PROCEDURE dbo.my_procedure AS BEGIN SET NOCOUNT ON; SELECT foo = 1, bar=”tooth”; END GO There is a quite convoluted way you can go about determining the metadata that the stored procedure will output. There are several caveats, including the procedure can … Read more
The connection pool calls sp_resetconnection before recycling a connection. Resetting the transaction isolation level is not in the list of things that sp_resetconnection does. That would explain why “serializable” leaks across pooled connections. I guess you could start each query by making sure it’s at the right isolation level: if not exists ( select * … Read more
Unfortunately SQL Server interprets the quoted comma as a delimiter. This applies to both BCP and bulk insert . From http://msdn.microsoft.com/en-us/library/ms191485%28v=sql.100%29.aspx If a terminator character occurs within the data, it is interpreted as a terminator, not as data, and the data after that character is interpreted as belonging to the next field or record. Therefore, … Read more
Because cursors take up memory and create locks. What you are really doing is attempting to force set-based technology into non-set based functionality. And, in all fairness, I should point out that cursors do have a use, but they are frowned upon because many folks who are not used to using set-based solutions use cursors … Read more
EF doesn’t support importing stored procedures which build result set from: Dynamic queries Temporary tables The reason is that to import the procedure EF must execute it. Such operation can be dangerous because it can trigger some changes in the database. Because of that EF uses special SQL command before it executes the stored procedure: … Read more
There are a number of ways to do this, depending on what you really want. With no common columns, you need to decide whether you want to introduce a common column or get the product. Let’s say you have the two tables: parts: custs: +—-+———-+ +—–+——+ | id | desc | | id | name … Read more
In SQL Server 2012 and higher, this will format a number with commas: select format([Number], ‘N0’) You can also change 0 to the number of decimal places you want.