SQL isn't run on the client side, so you can't "check" the timezone of the client in there. You'd need to check the client's date/timezone some other way and pass it in your SQL.

You could, instead, use datetimeoffset, if you're in a multi timezone environment. This stores the UTC settings as part of the time instead. For example:
SELECT SYSDATETIMEOFFSET();
--Returns 2017-08-21 09:24:15.2435981 +01:00
The +01:00 here represents the BST, as I am also in the UK.