Question:
Can I create a query converting the E3TimeStamp field from the E3Storage‘s table into DateTime?
Solution:
Yes, you can do this.
If you are using Oracle, you must use E3GetDateValue function, created automatically by E3. This function converts a number into a date. To visualize on the date/time format, also use TO_CHAR function.
Example:
Query.SQL = "SELECT TO_CHAR(E3GETDATEVALUE(E3TimeStamp),'DD/MM/YYYY HH:MI:SS')AS E3TimeStamp ,
FieldValue FROM <%TableName%> WHERE ( E3TimeStamp >= <%StartTime%> AND E3TimeStamp <= <%EndTime%>
AND FieldID = <%Fid%> ) ORDER BY E3TimeStamp"
To display date/time with the accuracy of milliseconds, add SSSSSS to the date format in the TO_CHAR command.
Example:
FROM <%TableName%>
Note: To create a filter by date, fill in <%StartTime%> and <%EndTime%> variables with date/time in Float or Double format. Use CDbl() function from the VBScript to convert a date into Double.
If you are using SQL, use CONVERT command: CONVERT ( data_type [ ( length ) ] , expression [ , style ] ).
Example:
Query.SQL = "SELECT FieldID,Convert(datetime,E3TimeStamp -2) AS E3TimeStamp,FieldValue FROM
<%TableName%> WHERE ( E3TimeStamp >= <%StartTime%> AND E3TimeStamp <= <%EndTime%> AND
FieldID = <%Fid%> ) ORDER BY E3TimeStamp"
Note: You must write “E3TimeStamp -2” in this case, because there is a difference between Elipse E3’s and SQL’s default calendars. The default date used by E3 is 30/12/1899 0:00:00; SQL, on the other hand, uses 01/01/1900 0:00:00. When recording the information in the database, E3 will use its default date; SQL, however, will use own date when converting.
