Can't select timestamp field on DB Connect App?

[AKG11]

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

[Neel]

Hello @AKG11  ,  we are facing a similar issue, and trying to get data via a Stored procedure, as below, but not able to select the date column for choosing the _time. Any ideas please ?

DECLARE @From_DT DATE, @To_DT DATE;
SET @From_DT =xxx
SET @To_DT = xxx
exec 'storedprocedure_name' @From_DT, @To_DT;

[ASplunker10]

We are encountering this issue too. Did you find a solution @AKG11 ?

[AKG11 · Accepted solution]

I have simplified my query. removed the first command " use  <DB>" as its not needed. you can define the db and table from dropdown.