MSDB..sysjobhistory issue

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  December 22, 2009 at 3:55 am

  #98357

  hi

  SELECT run_date , run_time

  FROM MSDB..sysjobhistory

  the above two columns are basically int type which contains date and time values

  my requirement is , i need to concatenate it to make it complete standard date

  for example its giving

  run_date , run_time

  20091222 , 162101

  output should be

  2009-12-22 16:21:01

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

• Nabha

  SSCrazy Eights

  Points: 8534

  More actions

  December 22, 2009 at 5:14 am

  #1095332

  Its all about formatting the data I guess, have you tried anything?

  ---------------------------------------------------------------------------------

• Sunny-138471

  Ten Centuries

  Points: 1162

  More actions

  December 22, 2009 at 5:20 am

  #1095333

  Here's the query:

  select convert(datetime,convert(varchar(100),run_date) + ' '

  + convert(varchar(2),run_time/10000)

  + ':' + convert(varchar(2),(run_time/100) % 100)

  + ':' + convert(varchar(2),run_time % 100))

  FROM MSDB..sysjobhistory

  Thanks

  Satish More

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  December 22, 2009 at 5:40 am

  #1095337

  thanks a lot

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  December 22, 2009 at 6:33 am

  #1095360

  Here is another option:

  select

  run_date,

  run_time,

  cast(cast(run_date as varchar(8)) + ' ' + stuff(stuff(right('000000' + cast(run_time as varchar(6)), 6),5,0,':'),3,0,':') as datetime)

  from

  msdb.dbo.sysjobhistory