Datetime Conversion problem

[ptheriault]

I'm working on writting a script to kill spids over 1 hour old that are in a sleeping status. The problem is the LastBatch column from sp_who2 is a varchar datatype. How can I convert this without losing the time? Here is my script so far.

DECLARE @strSQL varchar(255)
PRINT 'Killing Users'
PRINT '-----------------'

DECLARE @tmpUsers TABLE(
spid int,
STATUS varchar(50),
loginname varchar(50),
hostname varchar(50),
blk varchar(10),
dbname varchar(50),
cmd varchar(30),
CPUTIME varchar(255),
DISKIO varchar(255),
LASTBATCH Varchar(30),
ProgramName Varchar(255),
SPID2 int,
requestid int)

INSERT INTO @tmpUsers EXEC SP_WHO2


SELECT spid, dbname,LastBatch 
FROM @tmpUsers 
WHERE dbname = '1stComp'
AND STATUS = 'sleeping'


DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM @tmpUsers 
WHERE dbname = '1stComp'
AND STATUS = 'sleeping'
--AND LastBatch

DECLARE @spid varchar(10)
DECLARE @dbname2 varchar(40)
OPEN LoginCursor

FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
WHILE (@@fetch_status <> -1)
BEGIN
        IF (@@fetch_status <> -2)
        BEGIN
        PRINT 'Killing ' + @spid
        SET @strSQL = 'KILL ' + @spid
        EXEC (@strSQL)
        END
        FETCH NEXT FROM LoginCursor INTO  @spid, @dbname2
END

CLOSE LoginCursor
DEALLOCATE LoginCursor

GO

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

I'm sure this is just one way of many.

[tt][blue]
Set DATEFORMAT YMD

SELECT spid, dbname,Convert(DateTime, Convert(VarChar(4), Year(GetDate())) + '/' + LastBatch)
FROM @tmpUsers
[/blue][/tt]



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

The problem is that the year is missing (how dumb is that).

Personally, I don't like to rely on Set DATEFORMAT because it can produce hard to find bug. As such, you could also use this...

[tt][blue]
SELECT spid, dbname,Convert(DateTime, Replace(Convert(VarChar(4), Year(GetDate())) + '/' + LastBatch, '/', ''))
FROM @tmpUsers
[/blue][/tt]

I think this is slightly better than my previous version.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ptheriault]

Thanks George, I can't believe they didn't make it a datatime column.

This is what I got.

DECLARE @tmpUsers TABLE(
spid int,
STATUS varchar(50),
loginname varchar(50),
hostname varchar(50),
blk varchar(10),
dbname varchar(50),
cmd varchar(30),
CPUTIME varchar(255),
DISKIO varchar(255),
LASTBATCH Varchar(30),
ProgramName Varchar(255),
SPID2 int,
requestid int)

INSERT INTO @tmpUsers EXEC SP_WHO2


Set DATEFORMAT YMD

SELECT spid, dbname,Convert(DateTime, Replace(Convert(VarChar(4), Year(GetDate())) + '/' + LastBatch, '/', ''))
FROM @tmpUsers 
WHERE dbname = '1stComp'
AND STATUS = 'sleeping'
AND DATEDIFF(hour,Convert(DateTime, Replace(Convert(VarChar(4), Year(GetDate())) + '/' + LastBatch, '/', '')),getdate())>1

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

Since you are using the replace method, you should be able to remove the 'set dateformat ymd'.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ptheriault]

ah, I missed that.

Thanks for your help again!

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

No problem. I'm just glad that I was able to help.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Paul,

I just thought of a potential problem.

The code I wrote for you assumes that the last batch was created in the same year as the time you run your query. For most of the year, this will be fine. However, suppose a batch was started on 31 Dec 2007 11:40 PM. Then, this code is run on 1 Jan 2008 12:10 AM. The batch that was started on the 31st would appear to have been started on 31 Dec 2008 (which hasn't happened yet).

I think you can accommodate for this potential problem with a slight modification to your where clause.

WHERE dbname = '1stComp'
AND STATUS = 'sleeping'
AND [!](
	DATEDIFF(hour,Convert(DateTime, Replace(Convert(VarChar(4), Year(GetDate())) + '/' + LastBatch, '/', '')),getdate())>1
	Or GetDate() < Convert(DateTime, Replace(Convert(VarChar(4), Year(GetDate())) + '/' + LastBatch, '/', ''))
    )[/!]

Note: This is just a wild copy/paste. As such, the parenthesis may not be right.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ptheriault]

Thanks George,
I don't think that is going to be a problem. This is just a temp procedure till we can get CF working correctly.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[PDreyer]

SELECT spid
from master..sysprocesses (nolock)
WHERE dbid = db_id('1stComp')
AND STATUS = 'sleeping'
AND Last_Batch<dateadd(hh,-1,getdate())