You are here:
Handle Date Values
When CRM Analytics loads dates into a dataset, it breaks up each date into multiple
columns, such as day, week, month, quarter, and year, based on the calendar year. For example, if
you extract dates from a CreateDate column, CRM Analytics
generates columns such as CreateDate_Day and CreateDate_Week. If your fiscal year differs from the calendar year,
you can enable CRM Analytics to generate fiscal date columns as well.
Standard Date Fields
CRM Analytics generates the following date columns.
| Field Name | Field Type | Description |
|---|---|---|
| <date field name>_Second | Text | Number of seconds. If the date contains no seconds, value is 0. |
| <date field name>_Minute | Text | Number of minutes. If the date contains no minutes, value is 0. |
| <date field name>_Hour | Text | Number of hours. If the date contains no hours, value is 0. |
| <date field name>_Day | Text | Day of the month. |
| <date field name>_Week | Text | Week number in calendar year. |
| <date field name>_Month | Text | Month number in calendar year. |
| <date field name>_Quarter | Text | Quarter number in calendar year. |
| <date field name>_Year | Text | Calendar year. |
| <date field name>_Week_Fiscal | Text | Week number in fiscal year. |
| <date field name>_Month_Fiscal | Text | Month number in fiscal year. |
| <date field name>_Quarter_Fiscal | Text | Quarter number in fiscal year. |
| <date field name>_Year_Fiscal | Text | Fiscal year. |
| <date field name>_sec_epoch | Numeric | Number of seconds that have elapsed since January 1, 1970 (midnight UTC). |
| <date field name>_day_epoch | Numeric | Number of days that have elapsed since January 1, 1970 (midnight UTC). |
Inherited Custom Fiscal Years Fields
After you have defined a custom fiscal year in Salesforce and enabled the setting in CRM Analytics (see Custom Fiscal Year Support), have CRM Analytics inherit the custom fiscal year so your users can work with custom fiscal year data in SAQL queries and dashboards.
When CRM Analytics loads dates into a dataset as part of a dataflow, it generates multiple
fields that describe periods of time, such as day, week, and month. For example, when CRM
Analytics loads a date field called CreatedDate, it
generates fields like CreatedDate_Day, CreatedDate_Week, and CreatedDate_Month.
After CRM Analytics inherits custom fiscal years from Salesforce, CRM Analytics generates more
fields that describe the inherited custom fiscal years. The custom fiscal year fields are named
with the suffix _Fiscal. Continuing with the CreatedDate example, CRM Analytics generates fields like CreatedDate_Week_Fiscal, CreatedDate_Month_Fiscal, and CreatedDate_Quarter_Fiscal.
Here are the Fiscal fields CRM Analytics generates for each date field.
| Field Name | Field Type | Description |
|---|---|---|
| <date field name>_Week_Fiscal | Text | Week number in fiscal year. |
| <date field name>_Month_Fiscal | Text | Month number in fiscal year. |
| <date field name>_Quarter_Fiscal | Text | Quarter number in fiscal year. |
| <date field name>_Year_Fiscal | Numeric | Fiscal year. |
- Considerations for Date Columns
Confirm that date values in a column are uniform in format and time zone. Inconsistencies can occur within a data source and after merging data from multiple data sources. - Date Formats and Fiscal Dates for Source Data
You can set metadata attributes to control how dates are loaded into CRM Analytics and to enable CRM Analytics to generate fiscal date columns.
