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:
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 ...Creating custom data type
Custom data types are used to validate organization-specific data, such as employee ID, invoice ID, shipment tracking ID, or asset ID. By creating a custom data type, you can set a standard for your organization-specific columns in your data. This ...Column Explorer
The Column Explorer in Zoho DataPrep allows you to search, navigate and control the viewing options of the columns in a dataset. You can search and filter columns with invalid or missing data and fix them quickly. This helps you focus on the columns ...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 ...Duplicate Column
Zoho DataPrep allows you to duplicate columns present in your data. To duplicate columns 1. Right-click on a column header and select the Duplicate columns option from the context menu. 2. Select the columns from the Columns to duplicate dropdown. 3. ...