I know the title might be confusing but I wanted to get as much detail into it as possible.
I use a linked server connection to extract data from an Oracle Production database into a reporting database (let's not discuss the validity of the approach at this point - it was inherited).
I can run:
execute ('select sysdate from dual') at XXX;
and that matches
select getdate()
However:
execute ('select max(dstamp) from transaction_table') at XXX;
returns a time one hour ahead of NOW.
It all worked fine during the summer (BST), but the minute we switched back to GMT we're an hour out.
I'm sure I've read that SQL Server doesn't have an equivalent to sessiontimezone or other functionality that might facilitate an offset to reverse the "problem", but any help would be appreciated.
It's not the first time we've had the issue and the old solution was to manually add an hour to every datetime field via a function and then stop the addition at the switch. Yes, not ideal, but it worked at the time and we couldn't resolve it last time.
The SQL servers were rebuilt back in April (I think it was April) and the timezone set to GMT Standard Time. If anyone can help and needs more info to diagnose/develop a solution drop me a line.
Many thanks in advance
So this has to do with the difference between you SQL Server system time and your Oracle server system time. SYSDATE pulls the date from the system on which it is being run (The SQL Server server). CURRENT_DATE on the other hand runs on the Oracle server time. We can use this to our advantage. So I would change your query to the following:
SELECT MAX(dstamp)+(CURRENT_DATE-SYSDATE)
FROM transaction_table;
This will shift the MAX result to account for the difference in server times.