· Nov 22, 2017

DATEPART(hour, columnName) function takes logical mode (in memory stored data) as input to column while executing the script in 'Display' and 'ODBC' mode

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

Discussion (3)0
Log in or sign up to continue

as documented in

DATEPART delivers a %Integer value.
Differently to %Date which has different display, logical and ODBC presentation %Integer does not follow any specific presentation but is just Integer and nothing else.
And your input to DATEPART function is in any example the same: the logical value of TimeCreated.

To  cover your expectations you would need to adapt your input yourself. eg by %ODBCOUT() function.
or the related LogicalToDisplay function.

Your expectation that a data type %Integer does a revers research how it was calculated is just un-realistic.