SQL Data Types

1 Overview

bxp uses the MySQL engine to store data. The data can be stored as different data types e.g. dates, numbers, text. This article will give a brief description of some of the more commonly used data types in bxp and will explain how data types can be converted for reporting purposes. The keystats module allows users to create highly customised reports. Sometimes there is a requirement to sort, or compare data which has been entered into the form as if it's a number or date but it's stored and treated as text.

This wixi will explain how to mitigate against this occurring.

2 Data Types

There are many different data types in excel but some of the main ones are listed below.

Data Type	Explanation
text	A string containing numbers, letters and special characters.
int(m)	Any whole number is an integer and can be stored. The m represents how many charaters long the number can be
date	This can hold a date in the following format: YYYY-MM-DD
datetime	This holds a date and time in the following format: YYYY-MM-DD HH:MM:SS
decimal(m,d)	Any fractional number. The m is the total number of digits and the d is the number of digits after the decimal point.

In bxp, any fields that are added to a form are added as text fields. They are the most flexible and can store multiple formats. A user may, for example, add a new field to store whole numbers. This will be stored as a text field even if all the data matches the format of an int field. A user will therefore come across some very unusual behaviour if they attempt to order a result set by this column.

3 Ordering Numbers and Text

If a user builds a database with an int column and decides to run a SQL statement to order the result by this column, they would expect the numbers to be ordered from smallest to largest:

• 20
• 100
• 888
• 5000

However, if a user is creating a form in bxp and adds a field to a form to store the number, this will be stored as text. If a user orders this field it will be ordered alphabetically. So any number starting with a one will appear before a number starting with a two because it is handled alphabetically. The above list will be ordered like so:

• 100
• 20
• 5000
• 888

This could cause issues if a user creates a keystats report and wants to order by this column. However, there is a function that can be used to treat the data as a different data type.

4 Cast Function

In MySQL there is a function called Cast which is used to convert one column of data from one data type to another. A user may have a form in bxp with an id of 938. The field that contains the numbers is strCDA_938_field_0_6. The following SQL query will order the numbers alphabetically.

Select strCDA_938_field_0_6 From CDA_938 Order By strCDA_938_field_0_6 asc

The cast function can be used to treat one column as a different data type. If a text field is cast as an integer then any functions that are applied to the field will treat the data as integers provided the data is all numeric.

The format for the cast function is "CAST(field_id AS DATATYPE)". There are eight valid data types, these are: Binary, Char, Date, Datetime, Time, Decimal, Signed and Unsigned. If you want to convert a data type to an integer then it must be cast as either a signed or Unsigned. An unsigned int is a non-negative number. A signed int can be positive or negative. The above SQL query can be amended to sort the data as numbers like so:

Select strCDA_938_field_0_6 from cda_938 Order By Cast(strCDA_938_field_0_6 AS UNSIGNED) desc

5 Date Example

Another situation where the cast function can be useful is when a user needs to convert text to dates. A user may add a field to a form to hold dates, and will then need a keystats report to display data depending on the date contained in this field. The data will be stored as text and will conform to the datetime layout.

If a user tries to create a keystat that compares the dates, then the result set will be empty.

Select strCDA_938_field_0_5 from CDA_938 where strCDA_938_field_0_5 < '2016-08-26 00:00:00' And strCDA_938_field_0_5 > '2016-08-21 00:00:00'

This can be fixed by using cast. If the SQL statement is changed then it will pull back all the correct data.

Select strCDA_938_field_0_5 from CDA_938 where CAST(strCDA_938_field_0_5 AS datetime) < '2016-08-26 00:00:00' And Cast(strCDA_938_field_0_5 As datetime) > '2016-08-21 00:00:00'

There are several applications of this function. The above two are the more commonly used applications.