T-SQL

MS SQL: long running queries

Saturday, November 3rd, 2007

select spid,cmd,status,loginame,open_tran,
datediff (s,last_batch,getdate()) as [Waittime (s)]
from master..sysprocesses p
where open_tran > 0
and spid > 50
and datediff (s,last_batch,getdate()) > 30
and exists ( select * [...]

Converting timestamp to char

Friday, February 3rd, 2006

Sometimes it’s necessary to convert Sybase timestamp to something more readable
Warning! Timestamp is not date type!

declare @p timestamp
declare @s varchar(16)
declare @t1, @t2 varbinary(4)
//— from char to timestamp
@t1 = hextoint(substring(lower(@s),1,8))
@t2 = hextoint(substring(lower(@s),9,8))
@p = @t1 + @t2
//— from timestamp to char
@s=(lower(convert(varchar(8),intohex(substring(p,1,4))))+ lower(convert(varchar(8),intohex(substring(p,5,4))))

Truncate time part of datetime/smalldatetime value

Thursday, February 2nd, 2006

If you need to obtain only date part from datetime/smalldatetime value use this code
DECLARE @date datetime
SET @date = CONVERT(varchar(8), GETDATE(), 112)

Keep on coding