Date functions can be used to do calculations and formatting on Date values. Click on a function name below to continue:
|
|
Date
Description | Creates a date value from three numeric values. |
Remark | This function should be used to represent a date to other functions instead of representing a date as text. |
Example | Ex. Date(2012,7,4) - returns the date July 4th, 2012. |
DateAdd
Description | Returns the sum of a date and a quantity of time. |
Remark | DateAdd takes three input arguments. 1. A string representing the interval you want to add. The interval can be. 'yyyy' (year), 'y' (days), 'd' (days), 'w' (weeks), 'm' (months), 'h' (hours), 'n' (minutes), 's' (seconds), 'ww' (weeks of year), or 'q' (quarters). 2. A real number representing how much time you want to add to the date. 3. A DateValue. |
Example | Ex. DateAdd(‘h’,1,Now()) – returns the date and time 1 hour from now. |
DateDiff
Description | Returns the amount of time between two dates. |
Remark | DateDiff takes three input arguments. 1. A string representing the interval you want to add. The interval can be. 'yyyy' (year), 'y' (days), 'd' (days), 'w' (weeks), 'm' (months), 'h' (hours), 'n' (minutes), 's' (seconds), 'ww' (weeks of year), or 'q' (quarters). 2. The first date value. 3. The second date value. |
Example | Ex. DateDiff(“yyyy”, date(1787,9,17), now())- returns the number of years since the signing of the United States’ Constitution |
DateValue
Description | Converts a date represented as text (e.g. '30-jan-2008') to a date value. |
Remark | Any time information in the Date_text is ignored. The ticks returned always represent a time-of-day of Midnight (in the server's local time). If the year portion of Date_text is omitted, DATEVALUE uses the current year on the server. Use this function when comparing two dates. |
Example | Ex. DateValue(30-jun-2011) – returns the date object 6/30/2011. Ex. DateValue({Orders.OrderDate}) > DateValue(Today()) – compares the order date to today. |
Day
Description | Returns the day portion of a date as a whole number. |
Remark | Values returned by the YEAR, MONTH and DAY functions will be Gregorian Calendar values regardless of the display format for the supplied date value. |
Example | Ex. Day({Appointment.Date})- returns the day of the appointment. |
Day360
Description | Returns the number of days between two dates based on a 360-day year. |
Remark | Often used in accounting applications. Date360 takes three input arguments. 1. The first date value. 2. The second date value. 3. Optional: True/False indicating to use European or American method of computation. If not included the American method is used. |
Example | Ex. Day360({Appointment.Date},today())- returns the number of days between today and the appointment date. |
GlobalDateFormat
Description | Returns a DATE value whose format is based on the session format. |
Remark | Only accepts data objects as input. |
Example | Ex. GlobalDateFormat({Appointment.Date})- returns the date of the appointment based on the session format. |
GlobalDateTimeFormat
Description | Returns a DATETIME value whose format is based on the session format. |
Remark | Only accepts data objects as input. |
Example | Ex. GlobalDateTimeFormat({Appointment.Date})- returns the date and time of the appointment based on the session format. |
Hour
Description | Returns the hour of a time value ranging from 0 (12:00 AM) to 23 (11:00 PM). |
Remark | Times may be entered as text strings within quotation marks or a date time value. |
Example | Ex. Hour('2:50:05PM') – returns 14. |
Minute
Description | Returns the Minute of a time value ranging from 0 to 59. |
Remark | Times may be entered as text strings within quotation marks or a date time value. |
Example | Ex. Minute('2:50:05PM') – returns 50. |
Month
Description | Returns the month portion of a date as a whole number, ranging from 1 (January) to 12 (December). |
Remark | Values returned by the YEAR, MONTH and DAY functions will be Gregorian Calendar values regardless of the display format for the supplied date value. |
Example | Ex. Month({Appointment.Date})- returns the month of the appointment. |
Now
Description | Returns today’s date and time (in local server time). |
Remark | If embedding in other functions use Now(‘false’). |
Example | Ex. Now() - returns the current date and time. |
Second
Description | Returns the seconds of a time value ranging from 0 to 59. |
Remark | When a time omits seconds, 0 (zero) is assumed. Times may be entered as text strings within quotation marks or a date time value. |
Example | Ex. Second('2:50:05PM') – returns 5. |
Time
Description | Returns the number of ticks in a period of hours, minutes and seconds. |
Remark | This function should be used to represent a time to other functions instead of representing a time as text. Time takes three input arguments. 1. Hours 2. Minutes 3. Seconds |
Example | Ex. Time(14,50,5) – returns 534050000000. |
TimeFormat1
Description | Returns the time component of a DATETIME input formatted as 'hh:mm tt'. |
Remark | This function should be used to represent a time to other functions instead of representing a time as text. |
Example | Ex. Timeformat1({Appointment.Date}) – returns the appointment date in the format as ‘hh:mm tt’. |
TimeValue
Description | Convert a time represented in text (i.e., “HH-mm-ss”) into time values that can be passed to other functions. |
Remark | Acceptable formats include '5:55 PM' and '17:55'. A time separator is mandatory ('17:00' is acceptable, '1700' is not). If AM/PM is not present AM is assumed. When specifying AM or PM, do not use periods ('A.M.' or 'P.M.' will return an error). |
Example | Ex. TimeValue(Time(14,50,5))- returns the time object 14:50:05. |
Today
Description | Returns today’s date with no time component. |
Remark | If embedding in other functions use Today(‘false’). See the Now() function to get today's date with its time component. |
Example | Ex. Today()- returns the current date. Today(‘false’) returns the current date formatted as MM/dd/yyyy. |
Year
Description | Returns the year portion of a date as a whole number, ranging from 1 to 9999. |
Remark | --- |
Example | Ex. Year(today()) – returns 2011. |
Next Article: Report Functions - Financial