date string conversion...

neotokyo

SSCertifiable

Points: 7239
More actions
November 28, 2006 at 7:40 am

#72196

i'm trying to create a few custom queries using the
sysjobs & sysjobhistory tables.
i'm coming across the following 2 columns:
run_date int ---> Date the job or step started execution.
run_time int ---> Time the job or step started.
the [run_date] column comes out like: 20061128
the [run_time] column comes out like: 80000
the query is no problem, but i would like to convert
the run_date into some thing like: '06 Nov. 11 Tuesday
and convert the run_time into some thing like 8:00am.
not to make matters more complicated but i would also like to combine
the 2 columns so they appear as one in the output.
for example: '06 Nov. 11 Tuesday 8:00am etc.
would this be difficult to set up??
thanks in advance!
_________________________
Kory Becker

Hall of Fame

Points: 3585
More actions
November 28, 2006 at 8:30 am

#674379

Select
Top 1 SubString(Convert(Char(4), DatePart(year, Convert(DateTime, Convert(VarChar(36), SubString(Convert(VarChar(10), l_sjh.run_date), 5, 2)+'/'+
SubString(Convert(VarChar(10), l_sjh.run_date), 7, 2)+'/'+
SubString(Convert(VarChar(10), l_sjh.run_date), 1, 4), 101)+' '+
SubString(Case When Len(l_sjh.run_time) = 1 Then "00000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then "0000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then "000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then "00"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then "0"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 1, 2)+':'+
SubString(Case When Len(l_sjh.run_time) = 1 Then "00000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then "0000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then "000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then "00"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then "0"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 3, 2)+':'+
SubString(Case When Len(l_sjh.run_time) = 1 Then "00000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then "0000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then "000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then "00"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then "0"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 5, 2)))), 3, 2)+' '+
SubString(Convert(VarChar(36), DateName(month, Convert(DateTime, Convert(VarChar(36), SubString(Convert(VarChar(10), l_sjh.run_date), 5, 2)+'/'+
SubString(Convert(VarChar(10), l_sjh.run_date), 7, 2)+'/'+
SubString(Convert(VarChar(10), l_sjh.run_date), 1, 4), 101)+' '+
SubString(Case When Len(l_sjh.run_time) = 1 Then "00000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then "0000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then "000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then "00"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then "0"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 1, 2)+':'+
SubString(Case When Len(l_sjh.run_time) = 1 Then "00000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then "0000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then "000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then "00"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then "0"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 3, 2)+':'+
SubString(Case When Len(l_sjh.run_time) = 1 Then "00000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then "0000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then "000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then "00"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then "0"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 5, 2)))), 1, 3)+'. '+
Convert(Char(2), DatePart(dd, Convert(DateTime, Convert(VarChar(36), SubString(Convert(VarChar(10), l_sjh.run_date), 5, 2)+'/'+
SubString(Convert(VarChar(10), l_sjh.run_date), 7, 2)+'/'+
SubString(Convert(VarChar(10), l_sjh.run_date), 1, 4), 101)+' '+
SubString(Case When Len(l_sjh.run_time) = 1 Then "00000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then "0000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then "000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then "00"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then "0"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 1, 2)+':'+
SubString(Case When Len(l_sjh.run_time) = 1 Then "00000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then "0000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then "000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then "00"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then "0"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 3, 2)+':'+
SubString(Case When Len(l_sjh.run_time) = 1 Then "00000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then "0000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then "000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then "00"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then "0"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 5, 2))))+' '+
DateName(dw, Convert(DateTime, Convert(VarChar(36), SubString(Convert(VarChar(10), l_sjh.run_date), 5, 2)+'/'+
SubString(Convert(VarChar(10), l_sjh.run_date), 7, 2)+'/'+
SubString(Convert(VarChar(10), l_sjh.run_date), 1, 4), 101)+' '+
SubString(Case When Len(l_sjh.run_time) = 1 Then "00000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then "0000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then "000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then "00"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then "0"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 1, 2)+':'+
SubString(Case When Len(l_sjh.run_time) = 1 Then "00000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then "0000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then "000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then "00"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then "0"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 3, 2)+':'+
SubString(Case When Len(l_sjh.run_time) = 1 Then "00000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then "0000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then "000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then "00"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then "0"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 5, 2)))+' '+
Convert(Char(2), DatePart(hh, Convert(DateTime, Convert(VarChar(36), SubString(Convert(VarChar(10), l_sjh.run_date), 5, 2)+'/'+
SubString(Convert(VarChar(10), l_sjh.run_date), 7, 2)+'/'+
SubString(Convert(VarChar(10), l_sjh.run_date), 1, 4), 101)+' '+
SubString(Case When Len(l_sjh.run_time) = 1 Then "00000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then "0000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then "000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then "00"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then "0"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 1, 2)+':'+
SubString(Case When Len(l_sjh.run_time) = 1 Then "00000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then "0000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then "000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then "00"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then "0"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 3, 2)+':'+
SubString(Case When Len(l_sjh.run_time) = 1 Then "00000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then "0000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then "000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then "00"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then "0"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 5, 2))))+'hr '+
Convert(Char(2), DatePart(mi, Convert(DateTime, Convert(VarChar(36), SubString(Convert(VarChar(10), l_sjh.run_date), 5, 2)+'/'+
SubString(Convert(VarChar(10), l_sjh.run_date), 7, 2)+'/'+
SubString(Convert(VarChar(10), l_sjh.run_date), 1, 4), 101)+' '+
SubString(Case When Len(l_sjh.run_time) = 1 Then "00000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then "0000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then "000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then "00"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then "0"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 1, 2)+':'+
SubString(Case When Len(l_sjh.run_time) = 1 Then "00000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then "0000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then "000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then "00"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then "0"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 3, 2)+':'+
SubString(Case When Len(l_sjh.run_time) = 1 Then "00000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then "0000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then "000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then "00"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then "0"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 5, 2))))+'mn '+
Convert(Char(2), DatePart(ss, Convert(DateTime, Convert(VarChar(36), SubString(Convert(VarChar(10), l_sjh.run_date), 5, 2)+'/'+
SubString(Convert(VarChar(10), l_sjh.run_date), 7, 2)+'/'+
SubString(Convert(VarChar(10), l_sjh.run_date), 1, 4), 101)+' '+
SubString(Case When Len(l_sjh.run_time) = 1 Then "00000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then "0000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then "000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then "00"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then "0"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 1, 2)+':'+
SubString(Case When Len(l_sjh.run_time) = 1 Then "00000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then "0000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then "000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then "00"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then "0"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 3, 2)+':'+
SubString(Case When Len(l_sjh.run_time) = 1 Then "00000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then "0000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then "000"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then "00"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then "0"+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 5, 2))))+'sec '
From msdb.dbo.sysjobs l_sj
Inner Join msdb.dbo.sysjobhistory l_sjh
On l_sj.job_id = l_sjh.job_id
Order By "Run Date" Desc
,l_sj.name
neotokyo

SSCertifiable

Points: 7239
More actions
November 28, 2006 at 8:39 am

#674389

Y I K E S!
let me dive right into this and see if i can understand whats going on.
thanks
_________________________
RichB

SSCrazy Eights

Points: 9651
More actions
November 28, 2006 at 8:39 am

#674388

can you not simply use right('0000000' + ltrim(str(run_time)), 6) or whatever it is?

something along the lines of
select
cast(
ltrim(str(run_date))+ ' '+stuff(stuff(right('000000'+ltrim(str(run_time)), 6) , 3, 0, ':'), 6, 0, ':')
as datetime)
from sysjobhistory
MVDBA (Mike Vessey)

SSC-Insane

Points: 21797
More actions
November 28, 2006 at 8:40 am

#674390

select convert(datetime,
left(convert(varchar(8),run_date),4)+'-'+substring(convert(varchar(8),run_date),5,2)+'-'+right(convert(varchar(8),run_date),2)+' '+
case len(run_time) when 6 then
left(convert(varchar(6),run_time),2)+':'+substring(convert(varchar(6),run_time) ,3,2)+':'+right(convert(varchar(6),run_time),2)
else
left('0'+convert(varchar(6),run_time) ,2)+':'+substring('0'+convert(varchar(6),run_time) ,3,2)+':'+right('0'+convert(varchar(6),run_time) ,2)
end
)
from sysjobhistory
MVDBA
neotokyo

SSCertifiable

Points: 7239
More actions
November 28, 2006 at 8:53 am

#674399

Kory
getting alot of the following errors based on the zero's
Invalid column name '00000'
Invalid column name '0000'
Invalid column name '000'
Invalid column name '00'
Invalid column name '0'
etc...
_________________________
Ninja's_RGR'us

SSC Guru

Points: 294069
More actions
November 28, 2006 at 8:56 am

#674401

Replace the double quotes with single quotes.
neotokyo

SSCertifiable

Points: 7239
More actions
November 28, 2006 at 8:56 am

#674400

michael,
getting the following error from that script:
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting datetime from character string.
thoughts?
_________________________
neotokyo

SSCertifiable

Points: 7239
More actions
November 28, 2006 at 9:00 am

#674402

wangkhar
this works pretty good... thanks!!
use msdb
go
select
cast
(ltrim(str(run_date))+ ' '+stuff(stuff(right('000000'+ltrim(str(run_time)), 6) , 3, 0, ':'), 6, 0, ':')
as datetime) as 'last run' -- i just added this real quick.
from sysjobhistory
i'll check to see if i can work out a day conversion so i can see mon or tue with it,
but many thanks for the quick script!!
_________________________
neotokyo

SSCertifiable

Points: 7239
More actions
November 28, 2006 at 9:03 am

#674404

Ninja,
thanks for the heads-up with the single vs double quotes, but there
is an additional error:
Msg 408, Level 16, State 1, Line 2
A constant expression was encountered in the ORDER BY list, position 1.
a quick correction with the ( order by was all that was needed for this )
Order By [run_date] Desc
thanks for the quick check though
_________________________
neotokyo

SSCertifiable

Points: 7239
More actions
November 28, 2006 at 9:08 am

#674409

it's unreal the amount of conversion in the first reply though...
works great once you replace the (") double quotes with the (') single's,
and then a quick adjustment on the (order by)
again MANY THANKS for that Kory!
.
here is the full script again for any one else that might need it.
Select Top 1 SubString(Convert(Char(4), DatePart(year, Convert(DateTime, Convert(VarChar(36), SubString(Convert(VarChar(10), l_sjh.run_date), 5, 2)+'/'+
SubString(Convert(VarChar(10), l_sjh.run_date), 7, 2)+'/'+
SubString(Convert(VarChar(10), l_sjh.run_date), 1, 4), 101)+' '+
SubString(Case When Len(l_sjh.run_time) = 1 Then '00000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then '0000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then '000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then '00'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then '0'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 1, 2)+':'+
SubString(Case When Len(l_sjh.run_time) = 1 Then '00000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then '0000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then '000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then '00'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then '0'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 3, 2)+':'+
SubString(Case When Len(l_sjh.run_time) = 1 Then '00000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then '0000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then '000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then '00'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then '0'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 5, 2)))), 3, 2)+' '+
SubString(Convert(VarChar(36), DateName(month, Convert(DateTime, Convert(VarChar(36), SubString(Convert(VarChar(10), l_sjh.run_date), 5, 2)+'/'+
SubString(Convert(VarChar(10), l_sjh.run_date), 7, 2)+'/'+
SubString(Convert(VarChar(10), l_sjh.run_date), 1, 4), 101)+' '+
SubString(Case When Len(l_sjh.run_time) = 1 Then '00000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then '0000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then '000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then '00'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then '0'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 1, 2)+':'+
SubString(Case When Len(l_sjh.run_time) = 1 Then '00000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then '0000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then '000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then '00'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then '0'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 3, 2)+':'+
SubString(Case When Len(l_sjh.run_time) = 1 Then '00000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then '0000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then '000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then '00'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then '0'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 5, 2)))), 1, 3)+'. '+
Convert(Char(2), DatePart(dd, Convert(DateTime, Convert(VarChar(36), SubString(Convert(VarChar(10), l_sjh.run_date), 5, 2)+'/'+
SubString(Convert(VarChar(10), l_sjh.run_date), 7, 2)+'/'+
SubString(Convert(VarChar(10), l_sjh.run_date), 1, 4), 101)+' '+
SubString(Case When Len(l_sjh.run_time) = 1 Then '00000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then '0000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then '000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then '00'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then '0'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 1, 2)+':'+
SubString(Case When Len(l_sjh.run_time) = 1 Then '00000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then '0000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then '000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then '00'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then '0'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 3, 2)+':'+
SubString(Case When Len(l_sjh.run_time) = 1 Then '00000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then '0000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then '000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then '00'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then '0'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 5, 2))))+' '+
DateName(dw, Convert(DateTime, Convert(VarChar(36), SubString(Convert(VarChar(10), l_sjh.run_date), 5, 2)+'/'+
SubString(Convert(VarChar(10), l_sjh.run_date), 7, 2)+'/'+
SubString(Convert(VarChar(10), l_sjh.run_date), 1, 4), 101)+' '+
SubString(Case When Len(l_sjh.run_time) = 1 Then '00000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then '0000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then '000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then '00'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then '0'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 1, 2)+':'+
SubString(Case When Len(l_sjh.run_time) = 1 Then '00000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then '0000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then '000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then '00'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then '0'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 3, 2)+':'+
SubString(Case When Len(l_sjh.run_time) = 1 Then '00000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then '0000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then '000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then '00'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then '0'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 5, 2)))+' '+
Convert(Char(2), DatePart(hh, Convert(DateTime, Convert(VarChar(36), SubString(Convert(VarChar(10), l_sjh.run_date), 5, 2)+'/'+
SubString(Convert(VarChar(10), l_sjh.run_date), 7, 2)+'/'+
SubString(Convert(VarChar(10), l_sjh.run_date), 1, 4), 101)+' '+
SubString(Case When Len(l_sjh.run_time) = 1 Then '00000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then '0000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then '000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then '00'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then '0'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 1, 2)+':'+
SubString(Case When Len(l_sjh.run_time) = 1 Then '00000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then '0000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then '000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then '00'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then '0'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 3, 2)+':'+
SubString(Case When Len(l_sjh.run_time) = 1 Then '00000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then '0000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then '000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then '00'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then '0'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 5, 2))))+'hr '+
Convert(Char(2), DatePart(mi, Convert(DateTime, Convert(VarChar(36), SubString(Convert(VarChar(10), l_sjh.run_date), 5, 2)+'/'+
SubString(Convert(VarChar(10), l_sjh.run_date), 7, 2)+'/'+
SubString(Convert(VarChar(10), l_sjh.run_date), 1, 4), 101)+' '+
SubString(Case When Len(l_sjh.run_time) = 1 Then '00000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then '0000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then '000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then '00'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then '0'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 1, 2)+':'+
SubString(Case When Len(l_sjh.run_time) = 1 Then '00000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then '0000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then '000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then '00'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then '0'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 3, 2)+':'+
SubString(Case When Len(l_sjh.run_time) = 1 Then '00000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then '0000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then '000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then '00'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then '0'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 5, 2))))+'mn '+
Convert(Char(2), DatePart(ss, Convert(DateTime, Convert(VarChar(36), SubString(Convert(VarChar(10), l_sjh.run_date), 5, 2)+'/'+
SubString(Convert(VarChar(10), l_sjh.run_date), 7, 2)+'/'+
SubString(Convert(VarChar(10), l_sjh.run_date), 1, 4), 101)+' '+
SubString(Case When Len(l_sjh.run_time) = 1 Then '00000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then '0000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then '000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then '00'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then '0'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 1, 2)+':'+
SubString(Case When Len(l_sjh.run_time) = 1 Then '00000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then '0000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then '000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then '00'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then '0'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 3, 2)+':'+
SubString(Case When Len(l_sjh.run_time) = 1 Then '00000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 2 Then '0000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 3 Then '000'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 4 Then '00'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 5 Then '0'+Convert(VarChar(6), l_sjh.run_time)
When Len(l_sjh.run_time) = 6 Then Convert(VarChar(6), l_sjh.run_time) End, 5, 2))))+'sec '
From msdb.dbo.sysjobs l_sj
Inner Join msdb.dbo.sysjobhistory l_sjh
On l_sj.job_id = l_sjh.job_id
Order By [run_date] Desc
,l_sj.name
_________________________
Ninja's_RGR'us

SSC Guru

Points: 294069
More actions
November 28, 2006 at 9:12 am

#674411

Ya there are loads of faster way to make this convert, I would choose of of those VS reply 1. Not that it's wrong but I feel it's just too much maintenance work and coding work!
SQL ORACLE

One Orange Chip

Points: 27807
More actions
November 28, 2006 at 9:13 am

#674413

How about something like:
SELECT DATENAME(YEAR, CONVERT(DATETIME, run_date, 102)) +',' + DATENAME(MONTH, CONVERT(DATETIME, run_date, 102)) + ',' + DATENAME(DAY, CONVERT(DATETIME, run_date, 102)) = ',' DATENAME(WEEKDAY, CONVERT(DATETIME, run_date, 102))
Kory Becker

Hall of Fame

Points: 3585
More actions
November 28, 2006 at 9:15 am

#674416

I want to point out that run_time is an int and when a job runs one second after midnight the value will be 1 in the column causing the other sql to break.
neotokyo

SSCertifiable

Points: 7239
More actions
November 28, 2006 at 9:16 am

#674418

good idea SQL ORACLE,
but there are some minor adjustments
ie;
use msdb & run_date enclosed in brackets [run_date], and where the query is drawing from like (FROM sysjobhistory)
here it is again...
use msdb
go
SELECT DATENAME(YEAR, CONVERT(DATETIME, [run_date], 102)) + ',' + DATENAME(MONTH, CONVERT(DATETIME, [run_date], 102)) + ',' + DATENAME(DAY, CONVERT(DATETIME, [run_date], 102)) + ',' + DATENAME(WEEKDAY, CONVERT(DATETIME, [run_date], 102))
from sysjobhistory
but there was the following error on this any way:
Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type datetime.
_________________________

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply