Datafeed columns containing DateTime objects can formatted in many different ways within a dataset via the intuitive date functions provided as standard. For example, you may wish to display the Month that a date falls in or the Day of the week.
#Design Tip 1: It is sometime very useful to have a Date/Time stamp as a column in a datafeed to record the Date/Time the query was last run eg. SELECT GETDATE() AS DateTimeStamp FROM Tablename
#Design Tip 2: Having a Date/Time stamp column present in a datafeed allows Elapsed Time calculations to be performed within in the dataset using the inbuilt date difference function.
To format at date column in a dataset:
- Open an existing (or create) dataset Main Menu -> Dataset Designer -> Edit dataset
- Drag-and-drop the date column from the datafeed to the dataset.
- Open the dataset Column Property Dialogue box by clicking on the dataset column header. A true date attribute will have the DateTime Type. Alter the Column Name here:
Note: If the column shows the Type as String, the column has not been imported to the datafeed as a true DateTime type and the datafeed may need to be amended. - Specify how you would like to format the date in the Format section:
- Choose the Display type to suit using the drop-down:
Example: In this case we want to derive the Month from the DateTime Column - Choose the function you would like applied to display the Month:
In this case just the Month Name to be displayed - Choose the Start Month for month numbers i.e. If the start Month is set to April, April will be month 1 and March will be month 12. This allows for different financial year start dates.
- Choose the format you would like to display the Month i.e. Short/Long Jan/January
There are many different combinations of date formats that can be applied so listing every date function combination here not practical. The main display types are as follows and each has multiple functions and formats which can be applied:
- Simple date - Example 09/04/2020
- Time - Example 12:00 AM (Note that time must be present in DateTime column)
- Year - Example 2020
- Month - Example Month Number + Month Name Jan, January, 1, 12, 12-Jan
- Period - Example 20-12
- Week - 20-01
- Quarter - Reporting quarter
- Day - Day of week
- Date Difference - See below
Date Difference Function:
The Display Type: Function: date Difference gives the ability to compare two different date columns and display the difference between them i.e. 10 Days or 12 Months or 2 Years or 5 Minutes; a little like the standard DateDiff function in SQL:
Example: You may wish to calculate the difference in Days between the Document Date and the Due Date of an invoice:
Note: Current Column Position defines which way round the two dates are compared.
Comparing a date column to an absolute date
You may have a requirement to compare a DateTime column in the dataset with an absolute fixed date and time. i.e. How many days have elapsed since 01/01/1900
In this case, where you would normally select another DateTime column, there is a switch to allow you to enter an absolute date:
