Creating Formulas with Dates

Objectives

After completing this lesson, you will be able to:

  • Manipulate display of date/time data

Date Formulas Overview

Using date functions.

Date functions allow you to convert numbers to dates, which you can then format to display as appropriate, and to convert dates to numbers.

Calculations Using Date and Date/Time Fields

Examples of calculations using date or date/time fields include the following:

  • Current date function

  • Addition and subtraction

  • Year, month, and day functions

Current date function

To have the current day's date appear on a report, use the current date function. For example, to display today’s date in your report, you would write this formula:

Code snippet
CurrentDate
Expand

Addition and subtraction

You can add days to date fields by adding a number. For example, if you know that an order is shipped five days after the order was placed, you would find the expected ship date with this formula:

Code snippet
{Orders.Order Date} + 5
Expand

If {Orders.Order Date} was January 10, then this formula would return January 15.

You can also subtract days from date fields by subtracting a number. For example, if you want to find the number of days it took to ship an order, if the order date was five days before the ship date, you would use the formula:

Code snippet
{Orders.Order Date} – 5
Expand

If {Orders.Order Date} was January 10, then this formula would return January 5.

The most useful application of these concepts is finding the difference in days between two date fields. For example, if you want to find the number of days it took to ship an order, you would use this formula:

Code snippet
{Orders.Ship Date} – {Orders.Order Date}
Expand

If {Orders.Ship Date} was January 20 and {Orders.Order Date} was January 10, then this formula would return 10 days.

Year, month, and day functions

At times, you must extract the year, month, or day portions from a date field. To accomplish this task, you would use the Year ( ), Month ( ), or Day ( ) function. For example, if you were looking for monthly trends in your ordering cycle, you could use the Month ( ) function to extract the month portion of your order date fields using this formula:

Code snippet
Month({Orders.Order Date})
Expand

Hint
Commas and decimal places are the default display for numbers in Crystal Reports.

The DatePart Function

The DatePart function returns a number that specifies a given part of a given date.

  • intervalType is a String expression that specifies the part of a date to be returned.

  • inputDateTime is the DateTime value whose part is extracted.

  • firstDayOfWeek is an optional constant used to specify the first day of the week. If not specified, crSunday is assumed.

  • firstWeekOfYear is an optional constant specifying the first week of the year. If not specified, the first week is assumed to be the one in which Jan. 1 occurs (crFirstJan1).

The syntax is as follows:

  • DatePart(intervalType, inputDateTime)
  • DatePart(intervalType, inputDateTime, firstDayOfWeek)
  • DatePart(intervalType, inputDateTime, firstDayOfWeek, firstWeekOfYear)

DatePart Values and Descriptions

DatePartValueDescription
yyyyExtracts the year
qQuarter (the result is 1, 2, 3 or 4)
mMonth (the result is from 1 to 12)
yDay of year (1 to 365 or 366 in a leap year)
dDay part of the date (1 to 31)
wDay of week (1 to 7 with the result depending on firstDayOfWeek)
wwWeek of year (1 to 53 with firstDayOfWeek and firstWeekOfYear determining the exact days of the first calendar week of the year)
hExtracts the hour part of the given DateTime (0 to 23)
nMinute part (0 to 59)
sSecond part (0 to 59)

The following examples are applicable to both Basic syntax and Crystal syntax:

  • DatePart("d", #August 15, 1999#) returns 15.
  • DatePart("m", #August 15, 1999#) returns 8.
  • DatePart("n", #10:35 am#) returns 35.
  • DatePart("q", #September 29, 1999#) returns 3 since September 29 is in the third quarter of the year.
  • DatePart("ww", #September 14, 1997#) returns 38 since September 14, 1997 is in the 38th week of 1997.

The Date Function

The Date function converts any input to the equivalent date value. The syntax is as follows:

  • Date(number)
  • Date(string)
  • Date(dateTime)
  • Date(YYYY, MM, DD)

Date Arguments and Descriptions

DateArgumentsDescription
Date (number)number is a value representing the number of days starting from December 30, 1899. It can be positive or negative, and is truncated if fractional.
Date (string)string is a text string representing a date. Example: "September 20, 1999".
Date (dateTime)dateTime is a DateTime value.
Date (YYYY, MM, DD)
  • YYYY is a whole number representing a year. Example: 1996.
  • MM is a whole number representing a month. Example: 12 for December.
  • DD is a whole number representing a day of the month. Example: 05.

The following examples are applicable to Crystal syntax:

  • Date("Dec 31, 1999") returns the Date value for Dec. 31, 1999.
  • Date(50) returns the Date value for February 18, 1900.
  • Date(#Oct. 20, 1999 12:02pm#) returns the Date value for October 20, 1999.
  • Date(1930, 7, 30) returns the Date value for July 30, 1930.
Note
You can use the IsDate function to check if a String argument can be converted to a Date before doing the actual conversion. That way, if the conversion can't be done, you can handle the situation appropriately.

Log in to track your progress & complete quizzes