To set up a date method on a query column, you can do one of the following from Query Designer:

• In the row that specifies the column, set the Method Type to Date and the Method to Day, Month, and Year.
  
• Open the properties of the column, and then set the MethodType Property to Date and Method Property to Day, Month, and Year.
  

For more information about how to set up query columns and properties, see How to: Create Queries.

Important
You can only use a date method on fields that have a Date or DateTime data type. For additional information about how to use a date method on a field that has the DateTime data type, see Working with DateTime Data Types.

This topic uses the following sample table and query to demonstrate the different date methods.

Sample Sales Header Table

The following table contains data about sales orders for customers. The Order Date field has the data type of Date and the format DD-MM-YYYY, where DD is the day, MM is the month, and YYYY is the year.

Note
This is a simplified subset of the data that is found in table 36 Sales Header of the CRONUS International Ltd. demonstration database.

No.	Bill-to Name	Order Date
1000	Autohaus Meilberg KG	18-01-2013
5000	Autohaus Meilberg KG	21-05-2013
4000	Beef House	30-09-2011
3000	Deerfield Graphics Company	05-04-2012
3000	Deerfield Graphics Company	29-04-2012

Sample Query

The following illustration shows Query Designer for a query that retrieves data from the sample Sales Header table. The query includes a totals method that counts the total the number of records from the table included in the dataset.

Note
A column that applies a date function is still part of the group unlike columns that apply a totals method.

The Day method retrieves the day from the date expression of a field value in the query column. The day is returned as an integer, in the range of 1 to 31, which represents the day of the month. If the day in the date expression is 0, then 1 is returned.

Example

The Month method retrieves the month from the date expression of a field value in the query column. The month is returned as an integer, in the range of 1 to 12, where 1 represents January and 12 represents December. If the month in the date expression is 0, then 1 is returned.

Example

The Year method gets the year from the date expression of a field value in the query column. The year is returned as an integer. If the year in the date expression is 0, then 1900 is returned.

Example

On the SQL server, date and time values are processed using Coordinated Universal Time (UTC). If your Microsoft Dynamics NAV solution uses a time zone other than UTC and the field on which you apply the date method has a data type of DateTime, then there might be a difference between the date value that is returned in the dataset for the field and the actual day, month, or year for the field in the table. This occurs when the corresponding UTC date for a field falls on the next day or previous day because of the time of day and the time zone of Microsoft Dynamics NAV solution. The following table includes examples of DateTime values for two time zones that will return days, months, and years in a dataset that differ from the values in the table.

The differences in day, month, or year occur because when a date and time value is retrieved from the Microsoft Dynamics NAV database table, it is converted from the regional settings of the Microsoft Dynamics NAV solution to the UTC date and time. The day, month, or year is calculated on the SQL server, and then returned to the query dataset as an integer, which does not consider the regional settings of the Microsoft Dynamics NAV solution.

To avoid this condition, you should use the date method on fields that have a Date data type instead of a DateTime data type whenever possible. You can also return the DateTime value and implement post processing for the day, month, and year as needed.

Tasks

Reference

Concepts