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:
1CurrentDateAddition 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:
1{Orders.Order Date} + 5If {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:
1{Orders.Order Date} – 5If {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:
1{Orders.Ship Date} – {Orders.Order Date}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:
1Month({Orders.Order Date})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
| DatePartValue | Description |
|---|---|
| yyyy | Extracts the year |
| q | Quarter (the result is 1, 2, 3 or 4) |
| m | Month (the result is from 1 to 12) |
| y | Day of year (1 to 365 or 366 in a leap year) |
| d | Day part of the date (1 to 31) |
| w | Day of week (1 to 7 with the result depending on firstDayOfWeek) |
| ww | Week of year (1 to 53 with firstDayOfWeek and firstWeekOfYear determining the exact days of the first calendar week of the year) |
| h | Extracts the hour part of the given DateTime (0 to 23) |
| n | Minute part (0 to 59) |
| s | Second 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
| DateArguments | Description |
|---|---|
| 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) |
|
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.