Suppose that you want to see only the year in which your shell and tube heat exchangers were installed. All Equipment records in your database contain an Asset Installation Date field, and the values are stored in the format mm/dd/yyyy. You do not want to return the month or day in the query results.
In this case, you might configure a query on the Shell and Tube Heat Exchanger family, and add the Asset ID, Asset Description, and Year Installed fields to the query.
In this example, to return the year in which each shell and tube heat exchanger was installed, you would configure an expression using the TO CHAR function. You would also configure the alias to indicate that the column returns the year the item was installed.
In this case, the expression syntax is:
The syntax indicates that you want to see the Asset Installation Date field of Shell and Tube Heat Exchanger records and convert the returned values into a yyyy format. In other words, instead of seeing the installation dates in their stored format of mm/dd/yyyy, you will see only the year.
Other Possible Uses
- Reformat a numeric cost value so that it represents a currency value (e.g., reformat 125.75 as $125.75)
- Remove the day and year from a date and return only the month (e.g., reformat 12/15/2004 as 12)
The following table lists more examples of using the TO CHAR function to reformat dates.
| Parameter | Description | Example | Stored Value | Result |
|---|---|---|---|---|
| D | Displays a number representing the day of the week (1-7). | TO CHAR([Failure].[Failure Date],'D') | 12/1/2005 | 5 |
| DAY | Displays the name of the day in all capital letters. | TO CHAR([Failure].[Failure Date],'DAY') | 5/2/2005 | MONDAY |
| Day | Displays the name of the day with the first letter capitalized. | TO CHAR([Failure].[Failure Date],''Day') | 5/2/2005 | Monday |
| DY |
Displays the abbreviated name of the day. This parameter is not case-sensitive. You can enter DY, Dy, dy, or dY, and the result will be displayed with the first letter capitalized. |
TO CHAR([Failure].[Failure Date],'DY') | 12/1/2005 | Thu |
| DD | Displays a number representing the day of the month (1-31). | TO CHAR([Failure].[Failure Date],'DD') | 12/1/2005 | 01 |
| DDD | Displays a number representing the day of the year (1-366). | TO CHAR([Failure].[Failure Date],'DDD') | 12/1/2005 | 335 |
| MM | Displays a number representing the month of the year (01-12). | TO CHAR([Failure].[Failure Date],'MM') | 12/1/2005 | 12 |
| MON | Displays the abbreviated name of the month in all capital letters. | TO CHAR([Failure].[Failure Date],'MON') | 12/1/2005 | DEC |
| Mon | Displays the abbreviated name of the month with the first letter capitalized. | TO CHAR([Failure].[Failure Date],'Mon') | 12/1/2005 | Dec |
| MONTH | Displays the full name of the month in all capital letters (up to 9 characters). | TO CHAR([Failure].[Failure Date],'MONTH') | 6/8/2005 | JUNE |
| Month | Displays the full name of the month with the first letter capitalized (up to 9 characters). | TO CHAR([Failure].[Failure Date],'Month') | 6/8/2005 | June |
| Q | Displays a number representing the quarter of the year (1, 2, 3, 4, where January through March = 1). | TO CHAR([Failure].[Failure Date],'Q') | 12/1/2005 | 4 |
| WW | Displays a number representing the week of the year (1-53), where week one starts on the first day of the year and ends on the seventh day of the year. | TO CHAR([Failure].[Failure Date],'WW') | 12/1/2005 | 48 |
| W | Displays a number representing the week of the month (1-5), where week one starts on the first day of the month and ends on the seventh day of the month. | TO CHAR([Failure].[Failure Date],'W') | 12/1/2005 | 1 |
| YEAR | Displays the name of the year in all capital letters. | TO CHAR([Failure].[Failure Date],'YEAR') | 12/1/2005 | TWO THOUSAND FIVE |
| Year | Displays the name of the year with the first letter capitalized. | TO CHAR([Failure].[Failure Date],'Year') | 12/1/2005 | Two Thousand Five |
| YYYY | Displays a four-digit value representing the year. | TO CHAR([Failure].[Failure Date],'YYYY') | 12/1/2005 | 2005 |
| YYY,YY, Y | Displays a three-, two-, or one-digit value representing the year. | TO CHAR([Failure].[Failure Date],'YY') | 12/1/2005 | 05 |
The following table lists examples of using the TO_CHAR function to reformat numbers.
| Parameter | Description | Example | Stored Value | Result |
|---|---|---|---|---|
| , (comma) | Displays a comma in the specified position. | TO CHAR([Failure].[Total Failure Cost], '9,999') | 1234 | 1,234 |
| . (period) | Displays a decimal point in the specified position. | TO CHAR([Failure].[Total Failure Cost], '9,999.00') | 123411 | 1,234.11 |
| 9 | Displays a specific number of digits depending on the number of nines you include. Null values will be displayed for leading zeros. | TO CHAR([Failure].[Total Failure Cost], '999,999,999.00') | 12345.22 | 12,345.00 |
| $ | Displays a dollar symbol to the left of the value. | TO CHAR([Failure].[Total Failure Cost], '$999,999,999.00') | 12345.22 | $12,345.22 |
| S | Returns a plus sign (+) in the specified position for positive values and a negative sign (-) for negative values. | TO CHAR([Failure].[Total Failure Cost], 'S999,999,999.00') | 12345.22 | +12,345.22 |
| L | Displays the local currency symbol in this position (as determined by the Windows Regional Settings). | TO CHAR([Failure].[Total Failure Cost], 'L999,999,999.00') | 12345.22 | $12,345.22 (if local currency is US Dollars) |