Convert Datetime to TIME

• pskoasha

  SSC Veteran

  Points: 212

  More actions

  March 20, 2015 at 9:54 am

  #302324

  hi I have the following query it works with SQL2008 servers as TIME is a validate data type....but on SQL-2005 it fails can you help change the syntax for 2005 servers please:

  cast((select top 1 ISNULL(stop_execution_date, GETDATE()) - start_execution_date

  FROM [msdb].[dbo].[sysjobactivity]

  where job_id = r.job_id

  order by start_execution_date desc) as time) as Job_Duration,

  thanks for looking

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  March 20, 2015 at 10:06 am

  #1785410

  1. The TIME datatype does not exist in 2005 (it was introduced it 2008)

  2. Your query appears to have been cut off.

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• pskoasha

  SSC Veteran

  Points: 212

  More actions

  March 20, 2015 at 10:18 am

  #1785411

  it has been cut off...I needed help with it....I am aware of the DATA type in 2008 hence the help required for 2005

  anyway resolved for anybody who would need it:

  CONVERT(VARCHAR(12), (select top 1 ISNULL(stop_execution_date, GETDATE()) - start_execution_date

  FROM [msdb].[dbo].[sysjobactivity]

  where job_id = r.job_id

  order by start_execution_date desc), 14) AS Job_Duration ,