Get Records from SQL Data-SQL Server

[ManishKathuria1]

Hi Folks,

Using this Data-SQL Server Object to connect and get data from SQL. Whenever there is any Date field it changes the time to GMT time (e.g. we are using it in Singapore which is (GMT + 8:00 hours) so, when we read records from SQL the datetime shows -8 hours from original date time) 

Let say if it's 11/11/2020 9:00 AM at Server, what we will get from BP is 11/11/2020 1:00 AM

Any one know the fix for this other that calculating it manually and adding 8 hours in each record?


Thanks & Regards
Manish

------------------------------
Manish Kathuria
Application Devloper
IBM
America/Indiana/Indianapolis
------------------------------

[chris.mcgowan]

Hi Manish,

There is nothing in the Data - SQL Server Business Object that converts dates to GMT by default. I would check;

1. The data in SQL Server for your query to compare the results (speak to a DBA for assistance). It may be that the data is being persisted in database in GMT (more than likely UTC).
2. The Locale settings of the SQL Server instance.

Regards

Chris

------------------------------
Chris McGowan
Senior Technical Consultant
Blue Prism
------------------------------

[BrentonWestwood · Accepted solution]

Review the individual tables you are querying such as the Schedule Log, Session, or Work Queue table.    Some tables have date columns in GMT time and some have the dates saved in the local server time (time on the database).    Here are examples below of how I have evaluated time in my time zone (US Eastern Standard Time) where there is a need to convert the time and where there is not a need to convert the time.   I have one database server for the environment, thus everything is in the same time zone.  

Reviewing Schedule Logs for terminations in the last 24 hours and no session was created (good to see if a Schedule terminated and may not have even created a Session) -- need to CONVERT the date time -- adjust GMT by 5 hours:

SELECT
[id]
,[schedulelogid]
,[entrytype]
,FORMAT(([entrytime] - (RIGHT(CONVERT(datetime,SYSDATETIME()) AT TIME ZONE 'US Eastern Standard Time', 5))), 'MM-dd-yyyy hh:mm:ss tt') AS [Entry Time]
,[taskid]
,[logsessionnumber]
,[terminationreason]
,[stacktrace]
FROM [BLUE_PRISM_PR].[dbo].[BPAScheduleLogEntry]
WHERE [terminationreason] IS NOT NULL
AND [logsessionnumber] IS NULL
AND DATEDIFF(hour,(BLUE_PRISM_PR.dbo.BPAScheduleLogEntry.entrytime -
(RIGHT(CONVERT(datetime,SYSDATETIME()) AT TIME ZONE 'US Eastern Standard Time', 5))), SYSDATETIME()) <= 24
ORDER BY [entrytime] DESC

Yet, if you are looking to see what Sessions failed in the last 24 hours (where the Schedule started a session or someone triggered a run from the Control Room manually), you do not need to convert the time since it is presented in the local time zone:   

SELECT
dbo.BPVSessionInfo.sessionnumber AS [Session ID],
dbo.BPVSessionInfo.processname AS [Process],
dbo.BPVSessionInfo.runningresourcename AS [Resource],
dbo.BPVSessionInfo.starterusername AS [User],
dbo.BPAStatus.description AS [Status],
FORMAT(dbo.BPVSessionInfo.startdatetime, 'MM-d-yyyy hh:mm tt') AS [Start Time],
FORMAT(dbo.BPVSessionInfo.enddatetime, 'MM-d-yyyy hh:mm tt') AS [End Time]
FROM dbo.BPVSessionInfo INNER JOIN dbo.BPAStatus ON dbo.BPVSessionInfo.statusid = dbo.BPAStatus.statusid
WHERE
dbo.BPVSessionInfo.statusid = 2
AND dbo.BPVSessionInfo.starterusername = '[Scheduler]'
AND DATEDIFF(hour, dbo.BPVSessionInfo.startdatetime, SYSDATETIME()) <= 24

------------------------------
Brenton Westwood
Systems Analyst
Southern Company
------------------------------