Link to home
Start Free TrialLog in
Avatar of TRACEYMARY
TRACEYMARY

asked on

convert on date gives error

All this script is doing is displaying the dts jobs status.
if my date es.next_run_date is 0 then i get error doing convert so i put in a case

case es.next_run_date
            when  0 then
but this not working.



declare @enabled bit, @last_run_outcome varchar(20)

set @enabled  = 1
set @last_run_outcome = NULL
--as
set nocount on

create table #execution_status
(
    job_id                UNIQUEIDENTIFIER NOT NULL,
    last_run_date         INT              NOT NULL,
    last_run_time         INT              NOT NULL,
    next_run_date         INT              NOT NULL,
    next_run_time         INT              NOT NULL,
    next_run_schedule_id  INT              NOT NULL,
    requested_to_run      INT              NOT NULL,
    request_source        INT              NOT NULL,
    request_source_id     sysname          COLLATE database_default NULL,
    running               INT              NOT NULL,
    current_step          INT              NOT NULL,
    current_retry_attempt INT              NOT NULL,
    job_state             INT              NOT NULL
)

-- declare variables
declare
      @run_outcome tinyint

-- initialise variables
select @run_outcome =
      case @last_run_outcome
            when 'Failed' then 0
            when 'Succeeded' then 1
            when 'Cancelled' then 2
            else null
      end            

-- run xp_sqlagent_enum_jobs and store in the temp table created above
insert #execution_status
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin = 1, @job_owner = 'sa'

-- return the restricted result set to the caller
select
      sj.name as job_name,
        case es.job_state
            when 1 then 'Running'
            when 2 then 'Blocked'
            when 3 then 'Awaiting retry'
            when 4 then 'Idle'
            when 5 then 'Suspended'
            when 6 then 'Awaiting outcome of a step'
            when 7 then 'Performing completion actions'
            else 'Unknown'
      end as status,
      case sjs.last_run_outcome
            when 0 then 'Failed'
            when 1 then 'Succeeded'
            when 2 then 'Cancelled'
            else 'Unknown'
      end as last_run_outcome,
      case sj.enabled
            when 1 then 'True'
            when 0 then 'False'
            else 'Unknown'
      end as enabled,
      --es.last_run_date,
 
      DATENAME(dw, CONVERT(CHAR(8), es.last_run_date, 112) )
          + ', '
          + DATENAME(m, CONVERT(CHAR(8), es.last_run_date, 112))
         + CONVERT(VARCHAR(12), DAY(CONVERT(CHAR(8), es.last_run_date, 112)))
          + ', '
          + CONVERT(CHAR(4), YEAR(CONVERT(CHAR(8), es.last_run_date, 112))) as "last_run_date",


      + STUFF(STUFF(RIGHT('000000'
      + CONVERT(VARCHAR(8), es.last_run_time), 6), 5, 0, ':'), 3, 0, ':')  as last_run_time,


      --right('000000' + rtrim (convert(char(6), es.last_run_time)),6) as last_run_time,
      es.next_run_date,
-----HERE if i try to see if the next run date is zero i get error

      case es.next_run_date
            when  0 then
      DATENAME(dw, CONVERT(CHAR(8), es.next_run_date, 112) )
          + ', '
          + DATENAME(m, CONVERT(CHAR(8), es.next_run_date, 112))
         + CONVERT(VARCHAR(12), DAY(CONVERT(CHAR(8), es.next_run_date, 112)))
          + ', '
          + CONVERT(CHAR(4), YEAR(CONVERT(CHAR(8), es.next_run_date, 112)))  
      
      else
      es.next_run_date
      end,

*/


      right('000000' + rtrim (convert(char(6), es.next_run_time)),6) as next_run_time
from
      #execution_status es
inner join
      msdb.dbo.sysjobs sj on es.job_id = sj.job_id
inner join
      msdb.dbo.sysjobservers sjs on es.job_id = sjs.job_id
where
      sj.enabled = @enabled
and       (sjs.last_run_outcome = @run_outcome OR @run_outcome is null)
order by
      sj.name

drop table #execution_status
GO
ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of TRACEYMARY
TRACEYMARY

ASKER

case es.next_run_date
             when isdate(es.next_run_date)
            then
      DATENAME(dw, CONVERT(CHAR(8), es.next_run_date, 112) )
          + ', '
          + DATENAME(m, CONVERT(CHAR(8), es.next_run_date, 112))
         + CONVERT(VARCHAR(12), DAY(CONVERT(CHAR(8), es.next_run_date, 112)))
          + ', '
          + CONVERT(CHAR(4), YEAR(CONVERT(CHAR(8), es.next_run_date, 112)))  
      
      else
      es.next_run_date
      end,

still error...only want to do convert part if date valid...
case when isdate(es.next_run_date)= 1
          then
     DATENAME(dw, CONVERT(CHAR(8), es.next_run_date, 112) )
         + ', '
         + DATENAME(m, CONVERT(CHAR(8), es.next_run_date, 112))
        + CONVERT(VARCHAR(12), DAY(CONVERT(CHAR(8), es.next_run_date, 112)))
         + ', '
         + CONVERT(CHAR(4), YEAR(CONVERT(CHAR(8), es.next_run_date, 112)))  
Got it working...Cheers