December 28, 2009 at 6:52 am
Hi,
how i can convert the last_occurrence_time in the msdb..sysalerts to regular time like hh:mm:ss
THX.
December 28, 2009 at 10:07 am
You are going to need to convert to varchar and parse the parts. It will vary based on length. So before 10 am you'll have shorter lengths for the hour while the seconds and minutes should be the same.
Something like:
;WITH cteAlertTimes AS
(
SELECT
CONVERT(VARCHAR(8), last_occurrence_date) AS last_date,
CONVERT(VARCHAR(6), last_occurrence_time) AS last_time,
LEN(CONVERT(VARCHAR(6), last_occurrence_time)) AS len_last_time
FROM msdb.dbo.sysalerts AS S
)
SELECT
CONVERT(DATETIME, last_date + ' ' + CASE len_last_time
WHEN 6 THEN LEFT(last_time, 2) + ':' + SUBSTRING(last_time, 3, 2)
WHEN 5 THEN LEFT(last_time, 1) + ':' + SUBSTRING(last_time, 2, 2)
ELSE '00:' + LEFT(last_time, 2)
END + ':' + RIGHT(last_time, 2)) AS last_date_and_time_as_datetime
FROM
cteAlertTimes
Edit: Moved the start of the code block to the start of the code.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 29, 2009 at 1:54 pm
Admittedly, almost all of the effort to execute the above query will be spent retrieving the data pages from disk, and it would be difficult to measure the CPU time required to do the string manipulation. But it bothers the premature optimizer in me to cause the allocation and destruction of so many string objects unnecessarily. I don't know for a fact that SQL Server has to allocate and garbage-collect a CLR string object for every string literal, function, and operator in a query, but if it does then that query creates 23 string objects per row.
This query cuts the number of string objects down to 10 per row.
SELECT CAST(CAST(NULLIF(last_occurrence_date, 0) AS CHAR(8)) + ' ' +
STUFF(STUFF(RIGHT(CAST(last_occurrence_time + 1000000 AS CHAR(7)), 6),
5, 0, ':'), 3, 0, ':') AS DATETIME) AS last_date_and_time_as_datetime
FROM msdb.dbo.sysalerts
This query uses numeric methods and avoids strings completely.
WITH cteAlertTimes
AS ( SELECT last_occurrence_date / 10000 - 1900 AS yr ,
last_occurrence_date / 100 % 100 - 1 AS mo ,
last_occurrence_date % 100 - 1 AS dy ,
last_occurrence_time / 10000 AS hr ,
last_occurrence_time / 100 % 100 AS mi ,
last_occurrence_time % 100 AS sec
FROM msdb.dbo.sysalerts
WHERE last_occurrence_date > 0
)
SELECT DATEADD(second, sec,
DATEADD(minute, mi,
DATEADD(hour, hr,
DATEADD(month, mo, DATEADD(YEAR, yr, 0))
+ dy)))
FROM cteAlertTimes
Okay, I spent more time writing this than you'll ever save by using it. But I've already wasted enough money on post-holiday online clearance sales and had to find a cheaper way to pass the time.
December 29, 2009 at 2:01 pm
Maybe this will help. The AsOfDateTime column will return nulls if its an invalid date. Ofcourse if all you want is the datetime and not the other columns in the table you can drop the case statement and build a where clause to filter for last_occurrence_time>0. I use a Convert rather than a Cast its costlier but has a style param namely 112 for dates in the format of yyyymmdd
SELECT AsOfDateTime=case when last_occurrence_date>0
then convert(datetime,
convert(char(9),last_occurrence_date)
+stuff(stuff(convert(varchar(6),last_occurrence_time),3,0,':'),6,0,':')
,112)
end
FROM msdb.dbo.sysalerts
December 29, 2009 at 2:12 pm
Pratap,
I get a conversion error when I run your code.
Scott,
Your code is faster. Nice.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 29, 2009 at 2:39 pm
drop the convert(datetime from the code and run. See what filter condition needs to be added to the case/where,(it has to be one or more records in your table of a type that has not been logged in my sysalerts). Since it would then return a char column it should be visible. Needs just 2 dashs preceding the convert statement and the ,112)
SELECT AsOfDateTime=case when last_occurrence_date>0
then --convert(datetime,
convert(char(9),last_occurrence_date)
+stuff(stuff(convert(varchar(6),last_occurrence_time),3,0,':'),6,0,':')
--,112)
end
FROM msdb.dbo.sysalerts
December 30, 2009 at 12:10 pm
Pratap Prabhu (12/29/2009)
drop the convert(datetime from the code and run. See what filter condition needs to be added to the case/where,(it has to be one or more records in your table of a type that has not been logged in my sysalerts). Since it would then return a char column it should be visible. Needs just 2 dashs preceding the convert statement and the ,112)
SELECT AsOfDateTime=case when last_occurrence_date>0
then --convert(datetime,
convert(char(9),last_occurrence_date)
+stuff(stuff(convert(varchar(6),last_occurrence_time),3,0,':'),6,0,':')
--,112)
end
FROM msdb.dbo.sysalerts
Obviously you've not tested that against any times prior to 10AM. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply