IQA reference sheet
Use the SQL Expression field on the IQA Display tab to style various query properties.
Date and time
Display dates and times using any of the following styling formats.
Tip! For a list of all available date and time formats, see the Microsoft Documentation.
Returning only the year
Update a date property to only return the year using this syntax: YEAR(PROPERTYNAME)
- Example Input: YEAR(BIRTHDATE)
- Example Output: 1989
Note: To return the fiscal year, see Reporting using the fiscal year.
Changing the date display
Update the way date properties are displayed using this syntax: FORMAT(PROPERTYNAME, 'DesiredDateFormat')
| Example Input | Example Output |
|---|---|
| FORMAT(BIRTHDATE, 'MMMM dd, yyyy') | December 04, 2024 |
| FORMAT(BIRTHDATE, 'dddd, MMM dd, yyyy') | Wednesday, December 04, 2024 |
| FORMAT(BIRTHDATE, 'yyyy-MM-dd') | 2024-12-04 |
Changing the time display
Update a property that contains a time using this syntax: FORMAT(PROPERTYNAME, 'DesiredTimeFormat')
| Example Input | Example Output |
|---|---|
|
FORMAT(UPDATEDON, 'hh:mm tt') |
04:13 PM |
| FORMAT(UPDATEDON, 'MMM dd, yyy, hh:mm:ss tt') | Sep 10, 2024, 04:13:19 PM |
| FORMAT(UPDATEDON, 'HH:mm') | 16:13 |
Changing the date and time display
Update a property that contains a date and time using this syntax: FORMAT(PROPERTYNAME, 'DesiredDateFormat, DesiredTimeFormat')
| Example Input | Example Output |
|---|---|
| FORMAT(UPDATEDON, 'yyyy-MM-dd, HH:mm') |
2015-04-13, 16:28 |
| FORMAT(UPDATEDON, 'dddd, MMM dd, yyyy, hh:mm tt') | Monday, Apr 13, 2015, 04:28 PM |
| FORMAT(UPDATEDON, 'yyyy-MM-dd, HH:mm:ss') |
2015-04-13, 16:28:53 |
Text
Update any IQA property using these text formatting options.
| Type | Syntax | Example Input | Example Output |
|---|---|---|---|
| Bold | CONCAT('<b>', PROPERTYNAME, '</b>') | CONCAT('<b>', FULLNAME, '</b>') | Mr. Bernard H. Bufton |
| Italics | CONCAT('<i>', PROPERTYNAME, '</i>') | CONCAT('<i>', BIRTHDATE, '</i>') | Jan 30 1965 12:00AM |
| Underline | CONCAT('<u>', PROPERTYNAME, '</u>') | CONCAT('<u>', ID, '</u>') | 22683 |
| Color | CONCAT('<p style="color: color name;">', PROPERTYNAME, '</p>') | CONCAT('<p style="color: red;">', CHAPTER, '</p>') | NORTH |
| Color (using Hex codes) | CONCAT('<p style="color: #HexCode;">', PROPERTYNAME, '</p>') | CONCAT('<p style="color: #00A9E0 ;">', COUNTRY, '</p>') | United States |
| Color (using RGB codes) | CONCAT('<p style="color: rgb(#, #, #);">', PROPERTYNAME, '</p>') | CONCAT('<p style="color: rgb(0, 255, 0);">', CITY, '</p>') | Johnstown |
| Highlight | CONCAT('<mark>', PROPERTYNAME, '</mark>') | CONCAT('<mark>', FULLADDRESS, '</mark>') | PO Box 90481 Johnstown, PA UNITED STATES |
| Strikethrough | CONCAT('<s>', PROPERTYNAME, '</s>') | CONCAT('<s>', EMAIL, '</s>') | bernardbufton@mailinator.com |
