Combine INT to Datetime for last_occurrence_date and last_occurrence_time


 
 
--You can find a better solution from Scott Coleman


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

--better
;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
	--http://www.sqlservercentral.com/Forums/Topic839394-146-1.aspx
 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s