June 23, 2015 at 12:55 pm
Hi Experts,
While trying to change date format from INT to datetime getting the error.
Is this not possible to change "last_occurrence_date" from INT to Varchar?
select name, delay_between_responses, occurrence_count,
CAST(CAST(last_occurrence_date AS VARCHAR(10)) AS date) as last_occurrence_date,
--convert(datetime, last_occurrence_date, 121) as last_occurrence_date,
dateadd(hour, (last_occurrence_time / 1000000) % 100,
dateadd(minute, (last_occurrence_time / 10000) % 100,
dateadd(second, (last_occurrence_time / 100) % 100,
dateadd(millisecond, (last_occurrence_time % 100) * 10, cast('00:00:00' as time(2)))))) as 'last_occurrence_time [00:hh:mm:ss]'
, has_notification, event_description_keyword, notification_message, has_notification
from msdb.dbo.sysalerts
Order by occurrence_count desc
Please suggest and a wonderful day to you all.
Thanks.
June 23, 2015 at 1:09 pm
Can you provide an example of what 'last_occurrence_date' looks like before you try converting it?
Assuming the format is valid...
DECLARE @badDate INT = 20150623
SELECT
CONVERT(DATETIME,CONVERT(CHAR(8), @badDate)) AS GoodDate
I'm assuming you commented out line is where you had issues. You tried to convert from INT to DATETIME directly. The reason why it failed is because it adds the integers to the date 1900-01-01 but the maximum date is 9999-12-31. So today's date would exceed the maximum.
To illustrate:
DECLARE @test1 INT = 3
DECLARE @test2 INT = 2958463
DECLARE @test3 INT = 20150623 --today's date is too big so you get an error
SELECT
CONVERT(DATETIME, @test1, 121) AS ThreeDaysAdded,
CONVERT(DATETIME, @test2, 121) AS HighAsItCanGo
June 24, 2015 at 9:50 am
SQL-DBA-01 (6/23/2015)
Hi Experts,While trying to change date format from INT to datetime getting the error.
Is this not possible to change "last_occurrence_date" from INT to Varchar?
select name, delay_between_responses, occurrence_count,
CAST(CAST(last_occurrence_date AS VARCHAR(10)) AS date) as last_occurrence_date,
--convert(datetime, last_occurrence_date, 121) as last_occurrence_date,
dateadd(hour, (last_occurrence_time / 1000000) % 100,
dateadd(minute, (last_occurrence_time / 10000) % 100,
dateadd(second, (last_occurrence_time / 100) % 100,
dateadd(millisecond, (last_occurrence_time % 100) * 10, cast('00:00:00' as time(2)))))) as 'last_occurrence_time [00:hh:mm:ss]'
, has_notification, event_description_keyword, notification_message, has_notification
from msdb.dbo.sysalerts
Order by occurrence_count desc
Please suggest and a wonderful day to you all.
Try it this way:
SELECT name, delay_between_responses, occurrence_count,
CAST(CAST(last_occurrence_date AS VARCHAR(10)) AS date) AS last_occurrence_date,
CONVERT(datetime, CAST(last_occurrence_date AS varchar(8)), 121) AS last_occurrence_date,
DATEADD(hour, (last_occurrence_time / 1000000) % 100,
DATEADD(minute, (last_occurrence_time / 10000) % 100,
DATEADD(second, (last_occurrence_time / 100) % 100,
DATEADD(millisecond, (last_occurrence_time % 100) * 10, cast('00:00:00' as time(2)))))) AS 'last_occurrence_time [00:hh:mm:ss]',
has_notification, event_description_keyword, notification_message, has_notification
FROM msdb.dbo.sysalerts
ORDER BY occurrence_count DESC
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply