Format Two Datetimes’ Difference in [days in variable length] dd:hh:mm:ss (T-SQL)


--[hh:mm:ss]
create table test (Caseid int, indate datetime, outdate datetime)

Insert into test values(1,'2013-09-17 10:09:22.000' ,'2013-09-17 11:12:22.000')

Select Caseid, Convert(varchar(10), outdate-indate , 108) as [hh:mm:ss] from test t

SELECT Convert(varchar(8), dateadd(second, datediff(second, indate,outdate) ,'1900-01-01'), 108) as [hh:mm:ss]
from test
drop table test

----************************************
----**********************************

Declare @StartDate as datetime ='8/21/2013'
Declare @EndDate as datetime = current_timestamp --GETDATE()
--='8/18/2013'

SELECT @StartDate, @EndDate
, Coalesce( Convert(varchar(5),DateDiff(day, (@EndDate-@StartDate),'1900-01-01')) + ':'
+ Convert(varchar(10),(@EndDate-@StartDate), 108),'00:00:00:00') as [days:hh:mm:ss]

--SQL Server 2012 with FORMAT function
,Coalesce( Convert(varchar(5),DateDiff(day, (@EndDate-@StartDate),'1900-01-01')) + ':' +
FORMAT((@EndDate-@StartDate), 'HH:mm:ss') ,'00:00:00:00') as [SQL Server 2012--days:hh:mm:ss]

--Another sample for converting minutes to [hh:ss]

declare @diffinMinutes int=165

SELECT Convert(varchar(5),(dateadd(minute, @diffinMinutes,'1900-01-01')), 108) as [hh:mm]


SQL Server: Change Database Owner to SA

In SQL Server, when a database is created, the owner will be the user who created it.
This ownership gives the creator extra permissions which could cause potential security issues.
Another important issue is when the user (network user id) is not working at organization any more
and we don’t want this user to be the database owner obviously.

1.Find database owner:
We can find the owner of a database like this with T-SQL:

SELECT name, suser_sname(owner_sid) FROM sys.databases

 

2. Change database owner manually
We can change the owner of the database to sa from SSMS:

Right-click on database name>>Properties>>Files>>Owner>>Browser to find sa and clcik OK.
If we want to change all databases’ owner to sa in one step, you can run the following T-SQL script:

3.Change all user databases owner with T-SQL:


 

---Tested on SQL Server 2008

sp_msforeachdb
' USE [?]
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb'' AND ''?'' <> ''distribution''
BEGIN
 ALTER AUTHORIZATION ON DATABASE::[?] to sa;
END '

&nbsp;

Optimizing MERGE Statement Performance —link from MSDN

http://technet.microsoft.com/en-us/library/cc879317(v=sql.105).aspx


Calculating elapsed time between actions within tasks

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/beece289-b2fb-40ef-bf69-79a6784366fd/calculating-elapsed-time-between-actions-within-tasks

--Sample table and from RSingh, thanks.
DECLARE @TEMP TABLE(TaskID INT,ActionID INT,[User] INT,ActionDateAndTme DATETIME)
INSERT INTO @TEMP VALUES(1,11,1,'2008-07-02 08:45:00')
INSERT INTO @TEMP VALUES(1,12,1,'2008-07-02 08:50:00')
INSERT INTO @TEMP VALUES(1,13,2,'2008-08-02 09:45:00')
INSERT INTO @TEMP VALUES(2,11,1,'2008-10-02 08:45:00')
INSERT INTO @TEMP VALUES(2,12,1,'2008-11-02 08:45:00')
INSERT INTO @TEMP VALUES(2,13,2,'2008-11-02 08:50:00')</pre>
;
;with mycte as(select TaskID,ActionID,[User],ActionDateAndTme
,lag(ActionDateAndTme)
 Over(partition by TaskID Order by ActionID) preDT from @TEMP)
 , mycte1 as (Select TaskID,ActionID,[User]
 , datediff(s,preDT,ActionDateAndTme) as delta,
 preDT,ActionDateAndTme
  from mycte)
 Select TaskID,ActionID,[User]
 , Coalesce(RIGHT('0' + CONVERT(varchar(6), delta/86400),2)
 + ':' + RIGHT('0' + CONVERT(varchar(6), delta % 86400 / 3600), 2)
 + ':' + RIGHT('0' + CONVERT(varchar(2), (delta % 3600) / 60), 2)
 + ':' + RIGHT('0' + CONVERT(varchar(2), delta % 60), 2),'0') as [delta dd:hh:mm:ss]

  --For above format:http://adamhutson.com/2010/02/17/seconds-into-ddhhmmss-format/
--I have found an easy way to format
 , Coalesce(RIGHT('0' +Convert(varchar(10),DateDiff(day,'1900-01-01',(ActionDateAndTme-preDT))),2)  + ':'
 + Convert(varchar(10),(ActionDateAndTme-preDT), 108),'00:00:00:00') as [delta dd:hh:mm:ss--easy]

from mycte1 Order By TaskID,ActionID

For format datetime difference in dd:hh:mm:mm, please see another post:
https://jingyangli.wordpress.com/2013/08/29/format-two-datetime-diffrence-in-days-in-variable-length-ddhhmmss-t-sql/


CREATING A SQL SERVER DATABASE PROJECT IN VISUAL STUDIO 2012

A link from Michael Lang with description for how to create a database project in VS 2012:

http://candordeveloper.com/2013/01/08/creating-a-sql-server-database-project-in-visual-studio-2012/

 

 


Update SQL Agent Job’s Ownership Script

You can find a handy script to change ownership of SQL Agent jobs from this link posted by John Sterrett:

http://johnsterrett.com/2011/04/14/changing-sql-server-job-owners-against-the-sql-server-enterprise/