Hi, we are trying to configure a MSSQL query in DB Connect App. Some how we are not be able to select timestamp field.
Field is present in output results. Any idea what could be issue.
Query:
use XXXXX
DECLARE @OFF_SET NUMERIC(1), @PREV_START_DATE VARCHAR(21), @PREV_END_DATE VARCHAR(21)
SET @OFF_SET = (select DATEDIFF(HH, GETUTCDATE(), GETDATE()))
SET @PREV_START_DATE = (SELECT CONVERT(VARCHAR(11),DATEADD(d,-1,DATEADD(dd, DATEDIFF(d,0,GETDATE()), 0)),106)+' 00:00:00')
SET @PREV_END_DATE = (SELECT CONVERT(VARCHAR(11),DATEADD(d,-1,DATEADD(dd, DATEDIFF(d,0,GETDATE()),0)),106)+' 23:59:59')
SELECT
SUBSTRING(J.JOB_NAME,CHARINDEX ('_',J.JOB_NAME)+1, CHARINDEX ('_',J.JOB_NAME, CHARINDEX ('_',J.JOB_NAME)+1) - CHARINDEX ('_',J.JOB_NAME)-1) as APP_CODE,
J.JOB_NAME,
J.JOB_TYPE,
DATEADD(HH,@OFF_SET,R.START_DATE_TIME) START_DATE_TIME_EST,
DATEADD(HH,@OFF_SET,R.END_DATE_TIME) END_DATE_TIME_EST,
DATEDIFF(ss,DATEADD(HH,@OFF_SET,R.START_DATE_TIME),DATEADD(HH,@OFF_SET,R.END_DATE_TIME)) as RUN_TIME_IN_SECONDS,
S.NAME as JOB_STATUS,
R.EXIT_CODE
FROM
dbo.RPT_AS_JOB_DEF_DIMENSION J, dbo.RPT_AS_JOB_RUN_FACT R, dbo.RPT_AS_STATUS_DIMENSION S, dbo.RPT_AS_MACHINE_DIMENSION M
WHERE
J.JOB_DEF_ID=R.JOB_DEF_ID
and R.STATUS_ID=S.STATUS_ID
and R.RUN_MACHINE_ID=M.MACHINE_ID
and J.JOB_NAME like 'PAT_%'
and (DATEADD(HH,@OFF_SET,R.START_DATE_TIME) > @PREV_START_DATE AND DATEADD(HH,@OFF_SET,R.START_DATE_TIME) < @PREV_END_DATE)
ORDER BY SUBSTRING(J.JOB_NAME,CHARINDEX ('_',J.JOB_NAME)+1, CHARINDEX ('_',J.JOB_NAME, CHARINDEX ('_',J.JOB_NAME)+1) - CHARINDEX ('_',J.JOB_NAME)-1), R.START_DATE_TIME
