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

  • 0
  • 0
  • 170
  • 2
  • 1

Answers

as documented in
http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

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.

Thanks @Robert Camper. 

If the DATEPART() always takes logical mode value irrespective of display mode selected in SQL query viewer, I was wondering there is implicit conversion of  Timecreated Column(column1 in abobe example) to the mode selected in SQL query window.

Take a closer look to %Library.Date, %Library.TimeStamp, %Library.Integer
and you will see the various methods for LogicalTo.....
The switch you select applies to the final presentation only. Not to internal calculations.