CONVERT and CAST are essentially the same—there is no difference between their capabilities or performance. They both exist solely for historical reasons, not for any functional ones. As long as you understand that both of these SQL functions are used to change the data type of data stored in a table, you have this one 99 percent covered. In case you want to review something about data types, take a look back at Table 2.2, which describes some of the more common data types.
In the following snippet:
SELECT TIMESTAMP,
DATEADD(S, CAST([TIMESTAMP] AS DECIMAL(14, 4)), ‘19700101’) AS Timestamp2,
AF3gamma,
CONVERT(VARBINARY(8), CAST(AF3gamma AS DECIMAL(14, 4))) AS AF3GammaHex
FROM READINGCSV
the CAST function in the second line changes the string contained in the TIMESTAMP column to a DECIMAL and then converts it to a DATETIME, as you can see in Figure 2.26. The value in the original TIMESTAMP column is in Epoch time format. Converting the Epoch time format into a format most people are used to seeing requires some special handling, as you can see in the previous snippet. You need to use the DATEADD() function, passing it the interval, in this case S for seconds, followed by the amount to add to the date, which is provided as the last parameter.
FIGURE 2.26 Using CONVERT and CAST SQL commands
In the fourth line, CONVERT changes the data type in the AF3Gamma column, after it is cast to a DECIMAL, into HEX, just for fun.
COS, COT, LOG, SIN, STDEV, TAN, VAR
This group of functions consists of both aggregate (STDEV and VAR) and mathematical (COS, COT, LOG, SIN, and TAN) functions. Look at the following SQL query:
SELECT m.MODE, sc.SCENARIO, e.ELECTRODE, f.FREQUENCY, AVG([VALUE]) AS Average,
SUM([VALUE]) AS Total, STDEV([VALUE]) AS Deviation,
VAR([VALUE]) AS Variance,
TAN(AVG([VALUE])) AS TANGENTAVG, TAN(SUM([VALUE])) AS TANGENTSUM,
LOG(AVG([VALUE])) AS LOGARITHMAVG, LOG(SUM([VALUE])) AS LOGARITHMASUM,
COS(AVG([VALUE])) AS COSINEMAVG, COS(SUM([VALUE])) AS COSINESUM,
SIN(AVG([VALUE])) AS SINEMAVG, SIN(SUM([VALUE])) AS SINEASUM,
COT(AVG([VALUE])) AS COTANGENTAVG, COT(SUM([VALUE])) AS COTANGENTASUM
FROM MODE m, SCENARIO sc, [SESSION] s, ELECTRODE e, FREQUENCY f, READING r
WHERE m.MODE_ID = s.MODE_ID
AND sc.SCENARIO_ID = s.SCENARIO_ID
AND s.SESSION_ID = r.SESSION_ID
AND e.ELECTRODE_ID = r.ELECTRODE_ID
AND f.FREQUENCY_ID = r.FREQUENCY_ID
GROUP BY m.MODE, sc.SCENARIO, e.ELECTRODE, f.FREQUENCY
This query is experimental only. There may or may not be any significance of the cosine or sine value, for example, of the sum or average brainwave value based on the MODE, SCENARIO, ELECTRODE, and FREQUENCY. However, if there is some kind of trend that can be identified, then it can perhaps be used for driving the predictability of the scenario in which the brainwave is being captured in real time. Table 2.8 describes the functions implemented in the previous query in more detail.
TABLE 2.8 Aggregate and mathematical functions
| Function | Description |
| COS | Returns the trigonometric cosine of a provided value as a float data type |
| COT | Returns the trigonometric cotangent of a value as a float data type |
| LOG | Returns the logarithm of a given numeric value |
| SIN | Returns the trigonometric sine of a value as a float data type |
| STDEV | Returns the statistical standard deviation of all values in the defined expression |
| TAN | Returns the tangent of a provided value |
| VAR | Returns the statistical variance of all values in the defined expression |
There are a great deal of SQL functions that can be run against data. They are often bound to the specific type of DBMS. Oracle, SQL Server, and MySQL all have proprietary functions available only for the given DBMS.
