October 24, 2012 at 1:14 pm
Hi All,
I am querying the system tables to get SQL Agent Jobs and schedules information.
One field is the [msdb].[dbo].[sysschedules].[active_end_time] field that holds a time, e.g. 235959.
How can I format that to a time with the AM or PM.
I have tried ltrim(right(convert(varchar(8), [sysSched].[active_end_time], 100), 7)) but that still shows as 235959.
October 24, 2012 at 1:56 pm
Probably a better way, but this works:
declare @y int
set @y = 35959
select convert(varchar(20),convert(time,(substring((right(('0' + convert(varchar(6),@y)),6)),1,2) + ':' +
substring((right(('0' + convert(varchar(6),@y)),6)),3,2) + ':' +
substring((right(('0' + convert(varchar(6),@y)),6)),5,2))),100)
October 24, 2012 at 2:06 pm
stuff worked for me to convert it to an actual TIME datatype, but the AM/PM would be an extra step:
select
[active_end_time],
CONVERT(time,STUFF(STUFF(convert(varchar,[active_end_time]),3,0,':'),6,0,':') ) as val
from [msdb].[dbo].[sysschedules]
Lowell
October 24, 2012 at 2:10 pm
I get this error.
Msg 243, Level 16, State 1, Line 61
Type time is not a defined system type.
I cannot define types either. I have Read-Only access.
October 24, 2012 at 2:12 pm
Lowell's is better, just need to add the varchar convert:
select
[active_end_time],
convert(varchar(20),CONVERT(time,STUFF(STUFF(convert(varchar,[active_end_time]),3,0,':'),6,0,':')),100) as val
from [msdb].[dbo].[sysschedules]
October 24, 2012 at 2:13 pm
What version of SQL Server are you running? You posted this in the SQL Server 2008 forum, so I assumed you were on that version or better.
October 24, 2012 at 2:15 pm
David's got me thinking to day!
your time datatype error is because of SQL 2005, i assumed you wer ein 2008 because of the forum you picked.
this puts ont he AM/PM:
select
[active_end_time],
convert(varchar(20),STUFF(STUFF(convert(varchar,[active_end_time]),3,0,':'),6,0,':')) + CASE WHEN CONVERT(int,LEFT([active_end_time],2)) > 11 THEN ' PM' ELSE ' AM' END as val
from [msdb].[dbo].[sysschedules]
Lowell
October 24, 2012 at 2:20 pm
My bad, I confused myself by clicking the about link and that was for my local installed server...lol.
The current DB I am hitting is 2005, could be 2008 also; just depends.
I actually have 300 serevrs I have to go through....yikes!
October 24, 2012 at 2:23 pm
Lowell (10/24/2012)
David's got me thinking to day!your time datatype error is because of SQL 2005, i assumed you wer ein 2008 because of the forum you picked.
this puts ont he AM/PM:
select
[active_end_time],
convert(varchar(20),STUFF(STUFF(convert(varchar,[active_end_time]),3,0,':'),6,0,':')) + CASE WHEN CONVERT(int,LEFT([active_end_time],2)) > 11 THEN ' PM' ELSE ' AM' END as val
from [msdb].[dbo].[sysschedules]
I more thing, I may need to pad for a smaller time such as 75959 it is coming out like 75:95:9 PM
If not too hard in the 12 hour clock also.
October 24, 2012 at 2:35 pm
my sample data was all 6 characters;
this does the STUFF in reverse order, whhich would handle the 5 char times you are seeing sometimes.
select
[active_end_time],
REVERSE(STUFF(STUFF(REVERSE(convert(varchar,[active_end_time])),3,0,':'),6,0,':'))
+ CASE WHEN CONVERT(int,LEFT([active_end_time],2)) > 11 THEN ' PM' ELSE ' AM' END as val
from [msdb].[dbo].[sysschedules]
Lowell
October 24, 2012 at 2:41 pm
how many chances at this do i get to make it right again?
select
[active_end_time],
val ,
val + CASE WHEN CONVERT(int,SUBSTRING(val,1,CHARINDEX(':',val) -1)) > 11 THEN ' PM' ELSE ' AM' END as val
FROM (
select
[active_end_time],
REVERSE(STUFF(STUFF(REVERSE(convert(varchar,[active_end_time])),3,0,':'),6,0,':'))
as val
from [msdb].[dbo].[sysschedules]
)X
Lowell
October 24, 2012 at 2:42 pm
You are the man Lowell.
Thank you, it works perfectly.
Not sure how to mark as answer in this forum....
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply