About Date Functions

Suppose that all air cooled heat exchangers in your facility require inspection every six months. You might want to run a query to see the last inspection date of all air cooled heat exchangers and the date that is six months after that date. If each Air Cooled Heat Exchanger record in your database contains an Asset Inspection Date value, you could do so using the MI DateAdd function.

You might configure a query on the Air Cooled Heat Exchanger family, and add the Asset ID, Asset Description, Asset Inspection Date, and Next Inspection Date fields to the query.

In this example, to return the next inspection date, you would configure an expression using the MI DateAdd function. You would also configure the alias of this column to indicate that the column returns the next inspection date.

The expression syntax is:

The following table lists more examples of how you can use the MI DateAdd function.

Parameter	Example	Description	Stored Value	Result
yy	MI DateAdd('yy', 1, [Air Cooled Heat Exchanger].[Asset Installation Date])	Adds one year to the date on the Asset Installation Date field in Air Cooled Heat Exchanger records.	11/01/2009	11/01/2010
mm	MI DateAdd('mm', 6, [Air Cooled Heat Exchanger].[Asset Installation Date])	Add six months to the date on the Asset Installation Date field in Air Cooled Heat Exchanger records.	11/01/2009	05/01/2010
dd	MI DateAdd('dd', 4, [Air Cooled Heat Exchanger].[Asset Installation Date])	Adds four days to the date on the Asset Installation Date field in Air Cooled Heat Exchanger records.	11/01/2009	11/05/2009
hh	MI DateAdd('hh', 4, [Air Cooled Heat Exchanger].[Asset Installation Date])	Adds four hours to the date on the Asset Installation Date field in Air Cooled Heat Exchanger records.	11/01/2009 4:00:00	11/01/2009 8:00:00
mi	MI DateAdd('mi', 4, [Air Cooled Heat Exchanger].[Asset Installation Date])	Adds four minutes to the date on the Asset Installation Date field in Air Cooled Heat Exchanger records.	11/01/2009 4:00:00	11/01/2009 4:04:00
mi	MI DateAdd('mi', -4, [Air Cooled Heat Exchanger].[Asset Installation Date])	Subtracts four minutes from the date on the Asset Installation Date field in Air Cooled Heat Exchanger records.	11/01/2009 4:00:00	11/01/2009 3:56:00
ss	MI DateAdd('ss', 4, [Air Cooled Heat Exchanger].[Asset Installation Date])	Adds four seconds to the date on the Asset Installation Date field in Air Cooled Heat Exchanger records.	11/01/2009 4:00:00	11/01/2009 4:00:04

Suppose that all shell and tube heat exchangers in your facility require inspection every six months. You might want to run a query to see if the last inspection date of all shell and tube heat exchangers and the date that is six months after that date. If Shell and Tube Heat Exchanger records in your database contain an Asset Inspection Date value, you could do so using the ADD_MONTHS function.

You might configure a query on the Shell and Tube Heat Exchanger family, and add the Asset ID, Asset Description, Asset Inspection Date, and Next Inspection fields to the query.

In this example, to return the next date on which each shell and tube heat exchanger should be inspected, you would configure an expression using the ADD_MONTHS function. You would also configure the alias to indicate that the column returns the next inspection date.

The expression syntax is:

The following table lists more examples of how you can use the ADD MONTHS function.

Example Expression	Description	Stored Value	Result
ADD MONTHS([Failure].[Failure Date], 1)	Adds one month to the date on the Failure Date field in Failure records and displays the calculated date.	Failure Date: 11/01/2009	12/01/2009
ADD MONTHS([Failure].[Failure Date], -1)	Subtracts one month from the date on the Failure Date field in Failure records and displays the calculate date.	Failure Date: 11/01/2009	10/1/2009

Suppose that all air cooled heat exchangers in your facility require inspection every six months. You might want to run a query to see the last inspection date of all air cooled heat exchangers and the date that is six months after that date. If each Air Cooled Heat Exchanger record in your database contains an Asset Inspection Date value, you could do so using the DATEADD function.

You might configure a query on the Shell and Tube Heat Exchanger family, and add the Asset ID, Asset Description, Asset Inspection Date, and Next Inspection fields to the query.

In this example, to return the next inspection date, you would configure an expression using the DATEADD function. You would also configure the alias of this column to indicate that the column returns the next inspection date.

The expression syntax is:

The following table lists more examples of how you can use the DATEADD function.

Parameter	Example	Description	Stored Value	Result
yy	DATEADD('yy', 1, [Shell and Tube Heat Exchanger].[Asset Inspection Date])	Adds one year to the date on the Asset Inspection Date field in Shell and Tube Heat Exchanger records.	11/01/2009	11/01/2010
qq	DATEADD('qq', 1, [Shell and Tube Heat Exchanger].[Asset Inspection Date])	Adds three months to the date on the Asset Installation Date field in Shell and Tube Heat Exchanger records (i.e., one quarter).	11/01/2009	02/01/2010
mm	DATEADD('mm', 6, [Shell and Tube Heat Exchanger].[Asset Inspection Date])	Adds six months to the date on the Asset Installation Date field in Shell and Tube Heat Exchanger records.	11/01/2009	05/02/2010
ww	DATEADD('ww', 2, [Shell and Tube Heat Exchanger].[Asset Inspection Date])	Adds two weeks to the date on the Asset Installation Date field in Shell and Tube Heat Exchanger records (i.e., 14 days).	11/01/2009	11/15/2009
dd	DATEADD('dd', 4, [Shell and Tube Heat Exchanger].[Asset Inspection Date])	Adds four days to the date on the Asset Installation Date field in Shell and Tube Heat Exchanger records.	11/01/2009	11/05/2009
dd	DATEADD('dd', - 4, [Shell and Tube Heat Exchanger].[Asset Inspection Date])	Subtracts four days from the date on the Asset Installation Date field in Shell and Tube Heat Exchanger records.	11/01/2009	10/28/2009
dw	DATEADD('dw', 4, [Shell and Tube Heat Exchanger].[Asset Inspection Date])	Adds four days to the date on the Asset Installation Date field in Shell and Tube Heat Exchanger records.	11/01/2009	11/05/2009
dy	DATEADD('dy', 2, [Shell and Tube Heat Exchanger].[Asset Inspection Date])	Adds two days to the date on the Asset Installation Date field in Shell and Tube Heat Exchanger records.	11/01/2009	11/03/2009
hh	DATEADD('hh', 4, [Shell and Tube Heat Exchanger].[Asset Inspection Date])	Adds four hours to the date on the Asset Installation Date field in Shell and Tube Heat Exchanger records.	11/01/2009 4:00:00	11/01/2009 8:00:00
mi	DATEADD('mi', 4, [Shell and Tube Heat Exchanger].[Asset Inspection Date])	Adds four minutes to the date on the Asset Installation Date field in Shell and Tube Heat Exchanger records.	11/01/2009 4:00:00	11/01/2009 4:04:00
ss	DATEADD('ss', 4, [Shell and Tube Heat Exchanger].[Asset Inspection Date])	Adds four seconds to the date on the Asset Installation Date field in Shell and Tube Heat Exchanger records.	11/01/2009 4:00:00	11/01/2009 4:00:04

Suppose that you want to view only the month in which the shell and tube heat exchangers were installed in your facility. If all Shell and Tube Heat Exchanger records in your database contain an Asset Installation Date field, and the values are stored in the format mm/dd/yyyy, you could do so using the MI DatePart function.

You might configure a query on the Shell and Tube Heat Exchanger family, and add the Asset ID, Asset Description, and Installation Month fields to the query.

In this example, to return the month in which each shell and tube heat exchanger was installed, you would configure an expression using the MI DatePart function. You would also configure the alias to indicate that the column returns the year the piece of equipment was installed.

The expression syntax is:

Using this syntax, instead of displaying the installation date in the stored format of mm/dd/yyyy, the query results will display only the value representing the month.

The following table lists more examples of how you can use the MI DatePart function.

Parameter	Example	Description	Stored Value	Result
yy	MI DatePart('yy', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays only the year from a stored date value.	11/01/2009	2009
qq	MI DatePart('qq', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays a number representing the quarter of the year based on a stored date value.	11/01/2009	4
mm	MI DatePart('mm', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays only the month from a stored date value.	11/01/2009	11
ww	MI DatePart('ww', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays a number representing the week of the month based on a stored date value.	11/01/2009	1
dd	MI DatePart('dd', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays only the day from a stored date value.	11/01/2009	01
dw	MI DatePart('dw', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays a number representing the day of the week based on a stored date value (where 1 = Sunday, 2 = Monday, etc.).	11/01/2009	1
dy	MI DatePart('dy', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays a number representing the day of the year based on a stored date value.	11/01/2009	305
hh	MI DatePart('hh', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays only the hour from a stored date value.	11/01/2009 4:00:00	4
mi	MI DatePart('mi', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays only the minutes from a stored date value.	11/01/2009 4:00:00	00
ss	MI DatePart('ss', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays only the seconds from a stored date value.	11/01/2009 4:00:00	00

Suppose that you want to see the tasks that are assigned to a specific user and due in the next thirty days. If Task records contain the Next Date field that stores the date on which the task is due, you could do so using the NOW function.

You might configure a query on the Task family, and add the Task ID, Next Date, and Task Assigned To fields to the query.

In this example, you would configure an expression on the Next Date field using the NOW function. Additionally, you would configure a prompt to prompt the user for the name of the user whose Task records you want to view.

The expression syntax is:

The syntax indicates that you want to view the tasks that are due today and within the next thirty days. For example, if a task is due thirty-one days after today's date, it will not appear in the query results.

The following table lists more examples of how you can use the NOW function.

Example Expression	Description	Current Date	Result
NOW(0) or NOW()	Displays the current date.	11/01/2009	11/01/2009
NOW() - 3	Displays the date that is three days prior to the current date.	11/01/2009	10/29/2009
NOW() + 3	Displays the date that is three days later than the current date.	11/01/2009	11/04/2009

Suppose that you want to see all the air cooled heat exchanger failures in your facility that occurred in a given month. If each Air Cooled Heat Exchanger record is linked to a Failure record, and each Failure record contains the Failure Date/Time field that stores that date and time on which a failure occurred, you could do so using the LAST DAY function.

You might configure a query on the Air Cooled Heat Exchanger and Failure families, joined via the Asset Has Failure relationship, and add the Asset ID, Failure ID, Failure Date/Time, and Last Day fields to the query.

In this example, you would configure an expression using the LAST DAY function. Additionally, you would create a prompt that prompts you to select the last day of the month whose failures you want to view. You would configure the alias to indicate that the column displays the last day of the month.

The expression syntax is:

The prompt syntax is:

When you run the query, a prompt appears, where you can select 11/30/2009 12:00:00 A.M. to indicate that you want to view failures that occurred during the month of November.

After pieces of equipment are installed in your facility, you want to perform a quick check on them the following Monday. If all Equipment records in the database contain the field Installation Date, you could do so using the NEXT DAY function.

You might configure a query on the Equipment field, and add the Equipment ID, Equipment Short Description, Installation Date, and Following Monday fields to the query.

In this example, you would configure an expression using the NEXT DAY function. You would also configure the alias to indicate that the column displays the following Monday.

The expression syntax is:

The NEXT DAY function assumes that each week begins on Sunday and ends on Saturday. The expression includes a parameter indicating which day of the week you want to see in the results, where the value is between 1 and 7, where 1 represents Sunday and 7 represents Saturday.

In this example, the parameter 2 represents Monday. The syntax indicates that you want to see the date of the first Monday following the date stored in the Installation Date field.

The following table lists more examples of how you can use the NEXT_DAY function.

Parameters	Example	Description	Stored Value	Result
Sunday Monday Tuesday Wednesday Thursday Friday Saturday	NEXT DAY([Asset].[Asset Installation Date], 'Wednesday')	Displays the date of the Wednesday following the date stored on the Asset Installation Date field.	Sunday, 11/01/2009	Wednesday, 11/04/2009
1 2 3 4 5 6 7 Note: Each number corresponds to a day of the week in the order in which they occur, where 1 represents Sunday.	NEXT DAY([Asset].[Asset Installation Date], 4)	Displays the date of the Wednesday following the date stored on the Asset Installation Date field.	Sunday, 11/01/2009	Wednesday, 11/04/2009

After assets have been installed and have failed for the first time, you want to determine how many months passed between the installation date and the failure date. If all Air Cooled Heat Exchanger records contain the Asset Installation Date field, and all Failure records contain the Failure Date/Time field, then you can do so using the MONTHS BETWEEN function.

You might configure a query on the Air Cooled Heat Exchanger and Failure families, joined via the Asset Has Failure relationship, and add the Asset ID, Asset Description, Asset Installation Date, Failure ID, Failure Date/Time, and Intervals in Months fields to the query.

In this example, you would configure an expression using the MONTHS_BETWEEN function. You would also configure the alias to indicate that the column returns the number of months between installation and failure.

The expression syntax is:

When you run the query, the values in the Interval in Months column will contain decimal points. A more usable result would show rounded values without decimal points. You should, therefore, also use the TRUNC function so that the Interval in Months column displays only whole numbers. In this case, the entire expression syntax would be:

After pieces of equipment have been installed and have failed for the first time, you want to determine how many days passed between the installation date and the failure date. If all Air Cooled Heat Exchanger records contain the Asset Installation Date field, and all Failure records contain the Failure Date/Time field, then you can do so using the DATEDIFF function.

You might configure a query on the Air Cooled Heat Exchanger and Failure families, joined via the Asset Has Failure relationship, and add the Asset ID, Asset Description, Asset Installation Date, Failure ID, Failure Date/Time, and Difference fields to the query.

In this example, you would configure an expression using the DATEDIFF function. You would also configure the alias to indicate that the column displays the difference between the installation date and failure date.

In this case, the expression syntax is:

The following table lists more examples of how you can use the DATEDIFF function.

Parameters	Example	Description	Stored Values	Result
yy year	DATEDIFF('yy', [Air Cooled Heat Exchanger].[Asset Installation Date], [Failure].[Failure Date/Time])	Displays the difference in years between the dates stored on the Asset Installation Date and Failure Date/Time fields.	Asset Installation Date: 11/01/2009 Failure Date/Time: 12/14/2010	1
qq quarter	DATEDIFF('qq', [Air Cooled Heat Exchanger].[Asset Installation Date], [Failure].[Failure Date/Time])	Displays the difference between the dates stored on the Asset Installation Date and Failure Date/Time fields in increments of quarters.	Asset Installation Date: 11/01/2009 Failure Date/Time: 12/14/2010	4
mm month	DATEDIFF('month', [Air Cooled Heat Exchanger].[Asset Installation Date], [Failure].[Failure Date/Time])	Displays the difference in months between the dates stored on the Asset Installation Date and Failure Date/time fields.	Asset Installation Date: 11/01/2009 Failure Date/Time: 12/14/2010	13
ww week	DATEDIFF('week', [Air Cooled Heat Exchanger].[Asset Installation Date], [Failure].[Failure Date/Time])	Displays the difference in weeks between the dates stored on the Asset Installation Date and Failure Date/Time fields.	Asset Installation Date: 11/01/2009 Failure Date/Time: 12/14/2010	58
dd day dw dy dayofyear	DATEDIFF('dd', [Air Cooled Heat Exchanger].[Asset Installation Date], [Failure].[Failure Date/Time])	Displays the difference in days between the dates stored on the Asset Installation Date and Failure Date/Time fields.	Asset Installation Date: 11/01/2009 Failure Date/Time: 12/14/2010	409
hh hour	DATEDIFF('hour', [Air Cooled Heat Exchanger].[Asset Installation Date], [Failure].[Failure Date/Time])	Displays the difference in hours between the dates stored on the Asset Installation Date and Failure Date/Time fields.	Asset Installation Date: 11/01/2009 Failure Date/Time: 12/14/2010	9816
mi minute	DATEDIFF('mi', [Air Cooled Heat Exchanger].[Asset Installation Date], [Failure].[Failure Date/Time])	Displays the difference in minutes between the dates stored on the Asset Installation Date and Failure Date/Time fields.	Asset Installation Date: 11/01/2009 Failure Date/Time: 12/14/2010	588,960
ss second	DATEDIFF('second', [Air Cooled Heat Exchanger].[Asset Installation Date], [Failure].[Failure Date/Time])	Displays the difference in seconds between the dates stored on the Asset Installation Date and Failure Date/Time fields.	Asset Installation Date: 11/01/2009 Failure Date/Time: 12/14/2010	35,337,600

Suppose you want to convert the values on the Asset Service field in Air Cooled Heat Exchanger records to dates. For example, the values stored on the Asset Service field look something like the following: 11012009. You want to view this number in date format, like this: 11/01/2009.

In this case, you might configure a query on the Air Cooled Heat Exchanger family, and add the Asset ID, Asset Description, and Asset Service (Date) fields to the query.

In this example, to return the value on the Asset Service field formatted as a date, you would configure an expression using the TO DATE function. You would also configure the alias to indicate that the column returns the value on the Asset Service field as a date.

In this case, the expression syntax is:

...where 'MMDDYYYY' corresponds to the order in which the values representing the month (MM), day (DD), and year (YYYY) appear on the Asset Service field. In other words, the values on the Asset Service field appear like this: 11012009, where 11 represents the month (MM), 01 represents the day (DD), and 2009 represents the year (YYYY).

Note: The format of the parameter shown above ('MMDDYYYY') is an example. You should format the parameter to match the values that you want to convert.

Suppose that times are stored in Air Cooled Heat Exchanger Records in Eastern Standard Time, and you want to convert the values to Pacific Standard Time. If Air Cooled Heat Exchanger records contain the Asset Installation Date field, you could do so using the NEW TIME function.

You might configure a query on the Air Cooled Heat Exchanger family, and add the Asset ID, Asset Description, and Installation Date (PST) fields to the query.

In this example, you would configure an expression using the NEW TIME function. You would also configure the alias to indicate that the column displays installation dates in Pacific standard time.

The expression syntax is:

The following table lists more examples of how you can use the NEW TIME function.

Parameter	Description	Example	Stored Value	Result
AST ADT	Converts the stored time to Atlantic Standard or Daylight Time	NEW TIME([Air Cooled Heat Exchanger].[Asset Installation Date], 'EST', 'AST')	Asset Installation Date (EST): 10/01/2009 4:00:00 P.M.	Asset Installation Date (AST): 10/01/2009 5:00:00 P.M.
BST BDT	Converts the stored time to Bering Standard or Daylight Time.	NEW TIME([Air Cooled Heat Exchanger].[Asset Installation Date], 'EST', 'BST')	Asset Installation Date (EST): 10/01/2009 4:00:00 P.M.	Asset Installation Date (BST): 10/01/2009 9:00:00 A.M.
CST CDT	Converts the stored time to Central Standard or Daylight Time.	NEW TIME([Air Cooled Heat Exchanger].[Asset Installation Date], 'EST', 'CST')	Asset Installation Date (EST): 10/01/2009 4:00:00 P.M.	Asset Installation Date (CST): 10/01/2009 2:00:00 P.M.
EST EDT	Converts the stored time to Eastern Standard or Daylight Time.	NEW TIME([Air Cooled Heat Exchanger].[Asset Installation Date], 'EST', 'EST')	Asset Installation Date (PST): 10/01/2009 1:00:00 P.M.	Asset Installation Date (EST) 10/01/2009 4:00:00 P.M.
GMT	Converts the stored time to Greenwich Mean Time.	NEW TIME([Air Cooled Heat Exchanger].[Asset Installation Date], 'EST', 'GMT')	Asset Installation Date (EST): 10/01/2009 4:00:00 P.M.	Asset Installation Date (GMT): 10/01/2009 8:00:00 P.M.
HST HDT	Converts the stored time to Alaska-Hawaii Standard or Daylight Time.	NEW TIME([Air Cooled Heat Exchanger].[Asset Installation Date], 'EST', 'HST')	Asset Installation Date (EST): 10/01/2009 4:00:00 P.M.	Asset Installation Date (HST): 10/01/2009 10:00:00 A.M.
MST MDT	Converts the stored time to Mountain Standard or Daylight Time.	NEW TIME([Air Cooled Heat Exchanger].[Asset Installation Date], 'EST', 'MST')	Asset Installation Date (EST): 10/01/2009 4:00:00 P.M.	Asset Installation Date (MST): 10/01/2009 2:00:00 P.M.
NST	Converts the stored time to Newfoundland Standard Time.	NEW TIME([Air Cooled Heat Exchanger].[Asset Installation Date], 'EST', 'NST')	Asset Installation Date (EST): 10/01/2009 4:00:00 P.M.	Asset Installation Date (NST): 10/01/2009 5:30:00 P.M.
PST PDT	Converts the stored time to Pacific Standard or Daylight Time.	NEW TIME([Air Cooled Heat Exchanger].[Asset Installation Date], 'EST', 'PST')	Asset Installation Date (EST): 10/01/2009 4:00:00 P.M.	Asset Installation Date (PST): 10/01/2009 1:00:00 P.M.
YST YDT	Converts the stored time to Yukon Standard or Daylight Time.	NEW TIME([Air Cooled Heat Exchanger].[Asset Installation Date], 'EST', 'YST')	Asset Installation Date (EST): 10/01/2009 4:00:00 P.M.	Asset Installation Date (YST): 10/01/2009 11:00:00 A.M.

Suppose that you want to see the serial number of the air cooled heat exchangers in your facility even if the Asset Serial Number field in the Air Cooled Heat Exchanger records is empty, and if the Asset Serial Number field is empty, you want to see the value Not Defined.

In this case, you might configure a query on the Air Cooled Heat Exchanger family, and add the Asset ID, Asset Description, and Serial Numbers (ALL) fields to the query.

In this example, you would configure an expression using the NVL function. You would also configure the alias to indicate that the column returns serial numbers.

In this case, the expression syntax is:

...where Not Defined is the value that will appear where a null value is found on the Asset Serial Number field in the Air Cooled Heat Exchanger records.

Suppose that you want to see the installation date of the shell and tube heat exchangers in your facility even if the Asset Installation Date field in the Shell and Tube Heat Exchanger records is empty, and if the Asset Installation Date field is empty, you want to show the value Not Installed.

In this case, you might configure a query on the Shell and Tube Heat Exchanger family, and add the Asset ID, Asset Description, and Installation Dates (ALL) fields to the query.

In this example, you would configure an expression using the ISNULL function. You would also configure the alias to indicate that the column returns the installation dates.

In this case, the expression syntax is:

...where Not Installed is the value that will appear where a null value is found on the Asset Installation Date field in the Shell and Tube Heat Exchanger records.

Suppose that you want to see the values on the As Left fields for the analyzer instruments in your facility rounded to two decimal places. If all Analyzer Calibration records contain the As Left field, you can do so using the ROUND function.

You might configure a query on the Analyzer Calibration family, and add the Analyzer ID, Calibration Date, and As Left (rounded) fields to the query.

In this example, you would configure an expression using the ROUND function. You would also configure the alias to indicate that the column displays the rounded as left values.

The expression syntax is:

...where 2 is the number of decimal places to which the values will be rounded in the results.

Suppose that you want to see the number of months between when the air cooled heat exchangers were installed and when they failed for the first time, and you want to view that number in a truncated form. For example, instead of seeing the number of months in the format 3.33333, you want to see only 3.

You might configure a query on the Air Cooled Heat Exchanger and Failure families, joined via the Asset Has Failure relationship, and add the Asset ID, Asset Description, Asset Installation Date, Failure ID, Failure Date/Time, and Interval in Months fields to the query.

In this example, you would configure an expression using the TRUNC function and the MONTHS BETWEEN function. You would also configure the alias to indicate that the column returns the interval between installation and failure.

The expression syntax is:

Suppose that you want to see the name of the month in which the shell and tube heat exchangers were installed. Instead of 11, you want to see November, and you do not want to return the year or day in the query results. If all Shell and Tube Heat Exchanger records in your database contain an Asset Installation Date field, and the values are stored in the format mm/dd/yyyy (e.g., 11/01/2009), you could do so using the DATENAME function.

You might configure a query on the Shell and Tube Heat Exchanger family, and add the Asset ID, Asset Description, and Installed by Month fields to the query.

In this example, you would configure an expression using the DATENAME function. You would also configure the alias to indicate that the results display the month.

The expression syntax is:

...where mm indicates the part of the date (i.e., month) that you want to convert to its character format.

The following table lists more examples of how you can use the DATENAME function.

Parameter	Description	Example	Stored Value	Result
yy year	Displays a number representing the year from a stored date value.	DATENAME('year', [Shell and Tube Heat Exchanger].[Asset Installation Date])	11/01/2009	2009
qq quarter	Displays a number representing the quarter of the year based on a stored date value.	DATENAME('qq', [Shell and Tube Heat Exchanger].[Asset Installation Date])	11/01/2009	4
mm month	Displays the month from a stored date value.	DATENAME('mm', [Shell and Tube Heat Exchanger].[Asset Installation Date])	11/01/2009	November
ww	Displays a number representing the week of the month based on a stored date value.	DATENAME('ww', [Shell and Tube Heat Exchanger].[Asset Installation Date])	11/01/2009	1
dd day	Displays a number representing the day from a stored date value.	DATENAME('day', [Shell and Tube Heat Exchanger].[Asset Installation Date])	11/01/2009	01
dw	Displays the day of the week based on a stored date value.	DATENAME('dw', [Shell and Tube Heat Exchanger].[Asset Installation Date])	11/01/2009	Sunday
dy dayofyear	Displays a number representing the day of the year based on a stored date value.	DATENAME('dayofyear', [Shell and Tube Heat Exchanger].[Asset Installation Date])	11/01/2009	305

Suppose that you want to see the year in which the shell and tube heat exchangers were installed in your facility. If all Shell and Tube Heat Exchanger records in your database contain an Asset Installation Date field, and the values are stored in the format mm/dd/yyyy (e.g., 11/01/2009), you could do so using the DATEPART function.

You might configure a query on the Shell and Tube Heat Exchanger family, and add the Asset ID, Asset Description, and Installation Year fields to the query.

In this example, you would configure an expression using the DATEPART function. You would also configure the alias to indicate that the column displays the installation year.

The expression syntax is:

...where year is the portion of the date value that you want to view.

Parameter	Description	Example	Stored Value	Result
yy year	Displays a number representing the year from a stored date value.	DATEPART('yy', [Shell and Tube Heat Exchanger].[Asset Installation Date])	11/01/2009	2009
qq quarter	Displays a number representing the quarter of the year based on a stored date value.	DATEPART('quarter', [Shell and Tube Heat Exchanger].[Asset Installation Date])	11/01/2009	4
mm month	Displays a number representing the month from a stored date value.	DATEPART('mm', [Shell and Tube Heat Exchanger].[Asset Installation Date])	11/01/2009	11
ww week	Displays a number representing the week of the month based on a stored date value.	DATEPART('week', [Shell and Tube Heat Exchanger].[Asset Installation Date])	11/01/2009	1
dd day	Displays a number representing the day from a stored date value.	DATEPART('dd', [Shell and Tube Heat Exchanger].[Asset Installation Date])	11/01/2009	1
dw	Displays a number representing the day of the week based on a stored date value.	DATEPART('dw', [Shell and Tube Heat Exchanger].[Asset Installation Date])	11/01/2009	1 (i.e., Sunday)
dy dayofyear	Displays a number representing the day of the year based on a stored date value.	DATEPART('dy', [Shell and Tube Heat Exchanger].[Asset Installation Date])	11/01/2009	305