• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

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
0
TRACEYMARY
Asked:
TRACEYMARY
  • 2
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
TRACEYMARY,
> case es.next_run_date
>           when  0 then


Try using ISDATE()

CASE ISDate(es.next_run_Date) = 0 THEN   --- the value is not a valid date
0
 
TRACEYMARYAuthor Commented:
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...
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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)))  
0
 
TRACEYMARYAuthor Commented:
Got it working...Cheers
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now