Query
Hi All, In Cache Table i have stored the data value as horlog format ,by query how to retrive the data when i give the data field as date format.
Hi All, In Cache Table i have stored the data value as horlog format ,by query how to retrive the data when i give the data field as date format.
Use TO_DATE SQL function.
Or TO_CHAR.
Depending if you need convert string -> date (TO_DATE) or date -> string (TO_CHAR)
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:
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