Problem with STUFF

  • I like to get from the JobHistory table the run_date and the run_time and put them in a DateTime field. So found some STUFF examples in the internet and concocted my version. Which works fine if I use constants, but I run into a bit of a problem when using a variable.

    This is the statement with the constants:

    select distinct 'AEBDBS01', j.Name , j.description, j.enabled, h.run_status,

    stuff(stuff(right('000000' + convert(varchar(6),h.run_duration),6),5,0,':'),3,0,':'),

    convert(datetime,'20111003 ' + stuff(stuff(right('000000' + convert(varchar(6),'084312'),6),5,0,':'),3,0,':'))

    from [AEBDBS01].msdb.dbo.sysJobHistory h, [AEBDBS01].msdb.dbo.sysJobs j

    where j.job_id = h.job_id and h.run_date =

    (select max(hi.run_date) from [AEBDBS01].msdb.dbo.sysJobHistory hi

    where h.job_id = hi.job_id)

    The embolded values are to be replaced with what I find in the JobHistory table.

    When I replace ONLY the DATE-value (20111003) I receive an error. The statement becomes this:

    select distinct 'AEBDBS01', j.Name , j.description, j.enabled, h.run_status,

    stuff(stuff(right('000000' + convert(varchar(6),h.run_duration),6),5,0,':'),3,0,':'),

    convert(datetime,h.run_date + ' ' + stuff(stuff(right('000000' + convert(varchar(6),'084312'),6),5,0,':'),3,0,':'))

    from [AEBDBS01].msdb.dbo.sysJobHistory h, [AEBDBS01].msdb.dbo.sysJobs j

    where j.job_id = h.job_id and h.run_date =

    (select max(hi.run_date) from [AEBDBS01].msdb.dbo.sysJobHistory hi

    where h.job_id = hi.job_id)

    Error message:

    Msg 245, Level 16, State 1, Line 1

    Conversion failed when converting the varchar value '08:43:12' to data type int.

    I am wracking my brain over this. I think it has something to do with the internals of the STUFF function. The attribute run_date is defined as INT. AFAIK and can see, the run_date values in the table sysjobhistory are valid and all represent real dates, are integers. I just don't understand this.

    Can any1 shed a light? What am I missing?

    Greetz,
    Hans Brouwer

  • It's because run_date is an integer, and you're trying to add a string to it. Wrap h.run_date in a Cast/Convert to varchar, and it will do what you want.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Ah, I just found out and wanted to add it as a comment to my question.

    Tnx for answering though.

    Greetz,
    Hans Brouwer

Viewing 3 posts - 1 through 2 (of 2 total)

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