Can't select timestamp field on DB Connect App?

AKG11 — Tue, 06 Dec 2022 18:24:30 GMT

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

Re: Can't select timestamp field on DB Connect App?

ASplunker10 — Fri, 07 Jul 2023 18:15:20 GMT

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

Re: Can't select timestamp field on DB Connect App?

AKG11 — Mon, 10 Jul 2023 08:12:54 GMT

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

Re: Can't select timestamp field on DB Connect App?

Neel — Tue, 11 Jul 2023 16:57:57 GMT

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;

topic Re: Can't select timestamp field on DB Connect App? in Deployment Architecture

Can't select timestamp field on DB Connect App?

Re: Can't select timestamp field on DB Connect App?

Re: Can't select timestamp field on DB Connect App?

Re: Can't select timestamp field on DB Connect App?