Change data type
To change the data type of a column
- Text
- Number
- Decimal
- Timestamp
- Positive number
- Date
- Date time
- Time
- Duration
- Currency
- Length
- Temperature
- Boolean
- Email
- URL
- List
- Map
- Percentage
Format options
DATA TYPES |
FORMAT OPTIONS |
SUB-OPTIONS |
Text |
- |
- |
Number |
Thousands separator |
None, Comma (,), Dot (.) |
Decimal |
Thousands separator |
None, Comma (,), Dot (.) |
Decimal separator |
Comma (,), Dot (.) |
|
Time |
Time format |
|
Date |
Date format |
|
Date time |
Date time format |
|
Timestamp |
- |
- |
Currency |
Thousands separator |
None, Comma (,), Dot (.) |
Decimal separator |
Comma (,), Dot (.) |
|
Select unit |
Click here to know the units supported for currency datatype. |
|
Length |
Thousands separator |
None, Comma (,), Dot (.) |
Decimal separator |
Comma (,), Dot (.) |
|
Temperature |
Thousands separator |
None, Comma (,), Dot (.) |
Decimal separator |
Comma (,), Dot (.) |
|
Boolean |
- |
- |
- |
- |
|
URL |
- |
- |
List |
- |
- |
Map |
- |
- |
Duration |
Duration format |
|
Positive number |
- |
- |
Percentage |
- |
- |
To add a constraint
Data type |
Option |
Sub-options |
Text |
Wildcard |
Contains |
|
|
Doesn't contain |
|
|
Starts with |
|
|
Doesn't start with |
|
|
Ends with |
|
|
Doesn't end with |
|
|
In |
|
|
Not in |
|
|
Is |
|
|
Is not |
|
|
Patterns |
|
|
Regex |
|
String length comparators |
Length greater than(>) |
|
|
Length greater than or equal to(>=) |
|
|
Length equal to(=) |
|
|
Length not equal to() |
|
|
Length lesser than(<) |
|
|
Length less than or equal to(<=) |
|
|
Length range |
|
String length values |
In |
|
|
Not in |
Number |
Comparators |
Greater than(>) |
|
|
Greater than or equal to(>=) |
|
|
Equal to(=) |
|
|
Not equal to() |
|
|
Less than(<) |
|
|
Less than or equal to(<=) |
|
|
Range |
|
Individual values |
In |
|
|
Not in |
Decimal |
Comparators |
Greater than(>) |
|
|
Greater than or equal to(>=) |
|
|
Equal to(=) |
|
|
Not equal to |
|
|
Less than(<) |
|
|
Less than or equal to(<=) |
|
|
Range |
|
Individual values |
In |
|
|
Not in |
|
Precision |
NA |
Date, Date time |
Quarter |
Q1, Q2, Q3, Q4 |
|
Month |
January to December |
|
Week |
Week 1 to Week 53 |
|
|
Date
|
NA
|
|
Date & time |
NA |
|
Hours |
0 to 23 |
|
Day of the week |
Sunday to Saturday |
|
Day of the month |
1 to 31 |
|
Period(AM/PM) |
AM, PM |
|
Range |
NA |
Using the logical operators, you can combine the constraints and apply logic to determine the rule of precedence. The final expression is displayed in the Criteria expression box. You can click Edit to alter the default expression using logical operators and parenthesis to specify the precedence or the sequential order as to which constraint should be evaluated first. Click Save after making the required changes.
If you select the Date, Datetime or Time types
- You have to choose at least one tile from the Day, Month, and Year tiles.
- You can use the characters, 'Z' and 'T' only once in a datetime and a time format.
- The tiles that follow the character, 'T' must be related to time such as Hours, Mins, Sec.
- A datetime and time format must contain an Hours tile.
- In a datetime format, you can use either the character, 'Z ' or any one of the T.zone tiles.
- You can use any one of the T.zone tiles in a time format.
- You can choose only one format from the Day, Month, Year, Hours, Mins, and Sec tiles.
|
Date and Datetime delimiters
|
/ : - , . \s T Z
|
|
Time delimiters
|
/ : - , . \s Z
|
If you select the Duration data type
- The format cannot be empty.
- You can use a string only once in a format.
- For example, in the duration format, 301.201.21:12:00, the duration is repeated twice which makes it an invalid duration format.
- You can use either microsecond or millisecond in a duration format and not both.
- For example, in the duration format, 12 days 1 hours 2 minutes 1.911 seconds 1.910001 seconds, both microseconds and milliseconds are used which renders it an invalid duration format.
Access your files securely from anywhere
Zoho CRM Training Programs
Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.
Zoho DataPrep Personalized Demo
If you'd like a personalized walk-through of our data preparation tool, please request a demo and we'll be happy to show you how to get the best out of Zoho DataPrep.
- Zoho Workerly Home
- Forums
- Connect With Us:
- Zoho Recruit Home
- Forums
- Connect With Us:
All-in-one knowledge management and training platform for your employees and customers.
Create. Review. Publish.
You are currently viewing the help pages of Qntrl’s earlier version. Click here to view our latest version—Qntrl 3.0's help articles.
Zoho Sheet Resources
Zoho Forms Resources
Zoho Sign Resources
Sign, Paperless!
Zoho TeamInbox Resources
Design. Discuss. Deliver.
Related Articles
Dataset transforms
Dataset transform consists of combining and transforming datasets to suit your needs. You can apply the following transforms in a dataset. Deduplicate Row-wise Column-wise Join Append Pivot Unpivot Calculated columns Derive dataset Mark as personal ...Export data to Amazon RDS Oracle
Zoho DataPrep supports exporting data to Amazon RDS, a cloud database. Amazon RDS is also known as Amazon Relational Database Service and it helps to set up, operate, and scale databases. To export data to Amazon RDS Oracle 1. Open an existing ...Export data to Amazon RDS Maria DB
Zoho DataPrep supports exporting data to Amazon RDS, a cloud database. Amazon RDS is also known as Amazon Relational Database Service and it helps to set up, operate, and scale databases. To export data to Amazon RDS Maria DB 1. Open an existing ...Change date format
DataPrep offers a wide range of predefined date and time formats. Choose to apply a predefined datetime format from the template or create your own custom format. Your custom format is saved to the template and can be used again in the future. To ...Export data to Microsoft Azure-SQL Data Warehouse
Zoho DataPrep supports exporting data to Microsoft Azure under cloud database category. Azure is a cloud computing platform operated by Microsoft that provides access, management, and development of applications and services. To export data to ...