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

Posted: August 29, 2013 | Author: | Filed under: Uncategorized | Leave a comment 

--[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:mm]

declare @diffinMinutes int=165

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



--Another sample for converting minutes to [H:mm]

create table test (TotalTime varchar(5), EnteredLog  varchar(5))
Insert into test values
('10.00','6.40')
,('40.10','25.55')

--create table test (TotalTime decimal(4,2)
--,EnteredLog  decimal(4,2))
--Insert into test values
--(10.00,6.40)
--,(40.10,25.55)

Select 
Format( dateadd(Minute
,Datediff(Second,
try_cast(Replace('00:'+Cast(EnteredLog as varchar(5)),'.',':') as time(0)), 
try_cast(Replace('00:'++Cast(TotalTime as varchar(5)),'.',':') as time(0))),
Cast('1900-01-01' AS DATETIME))   
 ,'H.mm') [Hours.Minutes]

from test

drop table test

SQL Server: Change Database Owner to SA

Posted: August 28, 2013 | Author: | Filed under: SQL Server 2008, SQL Server 2012 | Leave a comment

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

Posted: August 26, 2013 | Author: | Filed under: SQL Server 2008 | Leave a comment

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

Calculating elapsed time between actions within tasks

Posted: August 23, 2013 | Author: | Filed under: SQL Server 2012 | Leave a comment

–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

Posted: August 22, 2013 | Author: | Filed under: Visual Studio | Leave a comment

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

Posted: August 22, 2013 | Author: | Filed under: SQL Server 2008 | 2 Comments

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/