August 18, 2010 at 9:52 am
I was trying to review the scheduled jobs on my servers (to add new maintenance jobs) and came upon an interesting problem. When this is run on my Production server (SQL Server 9.0.3042 Standard Edition/Win2003 32-bit) and my Development server (SQL Server 9.0.4237 Standard Edition/Win2003 32-bit) plus one of the new Archive servers (SQL Server 10.0.1600.22 Standard Edition/Win2008 both 64-bit) the queries work fine.
However, when I run it on another archive server (SQL Server 9.0.4237 Standard Edition/Win2008 32-bit) I receive a "Conversion failed when converting character string to smalldatetime data type." error message. When I comment out the ORDER BY clause, the query works fine? :crying:
USE MSDB
GO
SELECT J.Name
,SUBSTRING(CAST(S.Next_Run_Date as varchar),5,2)+'/'+CAST(RIGHT(S.Next_Run_Date,2) as varchar)+'/'+CAST(LEFT(S.Next_Run_Date,4) as varchar) as Next_Run_Date
,CASE WHEN LEN(Next_Run_Time) = 1 THEN '00:00:00'
WHEN LEN(Next_Run_Time) = 5 THEN '0'+CAST(LEFT(Next_Run_Time,1) as varchar)+':'+SUBSTRING(CAST(S.Next_Run_Time as varchar),2,2)+':'+CAST(RIGHT(Next_Run_Time,2) as varchar)
WHEN LEN(Next_Run_Time) = 6 THEN CAST(LEFT(Next_Run_Time,2) as varchar)+':'+SUBSTRING(CAST(S.Next_Run_Time as varchar),3,2)+':'+CAST(RIGHT(Next_Run_Time,2) as varchar)
ELSE CAST(Next_Run_Time as varchar) END as Next_Run_Time
,DATENAME(weekday,CAST(SUBSTRING(CAST(S.Next_Run_Date as varchar),5,2)+'/'+CAST(RIGHT(S.Next_Run_Date,2) as varchar)+'/'+CAST(LEFT(S.Next_Run_Date,4) as varchar) as smalldatetime)) as Next_Run_Day
,DATEPART(dd,CAST(SUBSTRING(CAST(S.Next_Run_Date as varchar),5,2)+'/'+CAST(RIGHT(S.Next_Run_Date,2) as varchar)+'/'+CAST(LEFT(S.Next_Run_Date,4) as varchar) as smalldatetime)) as RunWeekDay
from dbo.sysjobs J
INNER JOIN dbo.sysjobschedules S
ON J.Job_ID = S.Job_ID
WHERE J.Enabled = '1'
ORDER BY DATEPART(dd,CAST(SUBSTRING(CAST(S.Next_Run_Date as varchar),5,2)+'/'+CAST(RIGHT(S.Next_Run_Date,2) as varchar)+'/'+CAST(LEFT(S.Next_Run_Date,4) as varchar) as smalldatetime))
,CASE WHEN LEN(Next_Run_Time) = 1 THEN '00:00:00'
WHEN LEN(Next_Run_Time) = 5 THEN '0'+CAST(LEFT(Next_Run_Time,1) as varchar)+':'+SUBSTRING(CAST(S.Next_Run_Time as varchar),2,2)+':'+CAST(RIGHT(Next_Run_Time,2) as varchar)
WHEN LEN(Next_Run_Time) = 6 THEN CAST(LEFT(Next_Run_Time,2) as varchar)+':'+SUBSTRING(CAST(S.Next_Run_Time as varchar),3,2)+':'+CAST(RIGHT(Next_Run_Time,2) as varchar)
ELSE CAST(Next_Run_Time as varchar) END
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
August 18, 2010 at 10:42 am
edit:nevermind
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply