Query

These answers are all sufficient.  However, perhaps a more natural solution is to make the field of type %Date.  Then you can use the SelectMode to determine how the date is represented in a query.  For instance, look at the DOB field in Sample.Person (in the SAMPLES) namespace.  The storage looks like this:

^Sample.PersonD(1)=$lb(","Waterman,Brenda Z.","437-96-2023",36088,...)

Here we see the ID (1) the Name ("Waterman,Brenda Z.") the SSN (437-96-2023), and the DOB (36088).  If you want to see the data you can do the following:

--------------------------------------------------------------

SELECT Name,DOB FROM Sample.Person WHERE ID=1

Name                                     DOB

Waterman,Brenda Z.      36088

--------------------------------------------------------------

If we wanted to see that in a readable format, we can change the selectmode and get:

--------------------------------------------------------------

Name                                       DOB

Waterman,Brenda Z.   1939-10-22

--------------------------------------------------------------

Moreover, you can use this in the WHERE clause very naturally:

--------------------------------------------------------------

SELECT Name,DOB FROM Sample.Person WHERE DOB='1939-10-22'

Name                                           DOB

Waterman,Brenda Z.     1939-10-22

Underman,Jane V.          1939-10-22

Bush,Susan E.                   1939-10-22

--------------------------------------------------------------

The way to change the selectmode is going to depend on how you are running your query.  All queries that come through xDBC are in ODBC mode, so that's easy.  If you are running a dynamic SQL statement you can do:

s stmt.%SelectMode=1  

Possible values are:

• 0 for LOGICAL mode.
• 1 for ODBC mode.
• 2 for DISPLAY mode.

If you are using embedded SQL, you can use the precompiler directive #SQLCompile select=<value> as outlined here:

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

Hello

Are you storing 64365 or 64365,23587?

If you are storing the first then the above answers are correct.  If you are storing the second then you will need to create your own Data Type class based on %Timestamp and write your own LogicalToStorage and StorageToLogical methods.

Cache has system functions that will convert $H to a readable format and back

USER>W $zdatetime($h)
03/23/2017 06:36:38

USER>W $ZDATETIMEH("03/23/2017 06:36:38")
64365,23798

Brendan