Using the Date Method to Retrieve Day, Month, and Year Data

When you have fields in a table that contain dates, you can use a date method to retrieve only the year, month, or day instead of including the date in the resulting dataset of a query.

Setting Up a Date Method on a Query Column

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.

Hh166052.Important(en-us,NAV.70).gifImportant
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.

Sample Table and Query

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.

Hh166052.note(en-us,NAV.70).gifNote
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.

Query Designer showing a date method
Hh166052.note(en-us,NAV.70).gifNote
A column that applies a date function is still part of the group unlike columns that apply a totals method.

Day 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 following table displays the resulting dataset for the sample query with the Method property of the Order Date column set to Day.

Bill_to_Name Day_Order_Date Count_

Autohaus Meilberg KG

18

1

Autohaus Meilberg KG

21

1

Beef House

30

1

Deerfield Graphics Company

5

1

Deerfield Graphics Company

29

1

Month Method

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 following table displays the resulting dataset for the sample query with the Method property of the Order Date column set to Month.

Bill_to_Name Month_Order_Date Count_

Autohaus Meilberg KG

1

1

Autohaus Meilberg KG

5

1

Beef House

9

1

Deerfield Graphics Company

4

2

Year Method

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

The following table displays the resulting dataset for the sample query with the Method property of the Order Date column set to Year.

Customer Name Year_Order_Date Count_

Autohaus Meilberg KG

2013

2

Beef House

2011

1

Deerfield Graphics Company

2012

2

Working with DateTime Data Types

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.

Time Zone Date and Time in Microsoft Dynamics NAV Day returned by Day method Month returned by Month method Year returned by Year method

Pacific Time (UTC –8:00:00)

12-31-2011 17:00:00

1

1

2012

Middle European Time (UTC +1:00:00)

01-01-2012 00:59:00

31

12

2011

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.

See Also

Show: