I ran the below query in three different modes. Coordinated Universal Time is 5 hours ahead of Eastern Time so there is difference in value from column 1 and column 2 in ODBC, Display mode, but not in Logical Mode. By default the query executes in ODBC mode when we query the data from outside world(via ODBC connection).
I don't know why query 2 and query 3 outputs different from query 1.
Query 1. Ran in Logical mode, DATEPART() took in memory stored timestamp data(stored in UTC)
'Hour ' returned are same for both the columns
Query 2. Ran in ODBC mode, DATEPART() took in memory stored timestamp data(stored in UTC) ,but column1 converts data from logical mode to display mode while column 2 is still in logical mode .
Query 3. Ran in Display mode. Results same as query 2