Date/Time

Date functions allow you to convert numbers to dates and dates to numbers.

CurrentDate()

This function returns the current date on a report.

Return value

The return value is a date.

Example

If today is Oct. 6,1999, the return value of the following statement is 10/06/99.

CurrentDate()

CurrentDateTime()

This function returns the current date and time on a report.

Return value

The return value is a DateTime.

Example

If today is Oct.6,1999, the current time is 5:10:27 p.m., the return value of the following statement is 1999-10-06 05:10:27 PM.

CurrentDateTime()

CurrentTime()

This function returns the current time on a report.

Return value

The return value is a Time.

Example

If the current time is 5:10:27 p.m., the return value of the following statement is 05:10:27 PM.

CurrentTime()

DateAdd(intervalType, nIntervals, startDateTime)

Returns a DateTime value to which a specified number of time intervals have been added.

This function is used to add interval of time to a DateTime. Its main feature is that the DateTime returned will always be valid. For example, the function takes into account such factors as the number of days in a month and leap years. If you want to add or subtract days to a DateTime, you could use the addition and subtraction operators instead of the function with the "d" parameter. However, this function also handles other types of intervals such as adding months or hours to a DateTime.

Parameters

Return value

A DateTime value.

Examples

Notes:

DateDiff(intervalType, startDateTime, endDateTime, firstDayOfWeek)

Returns a number of time intervals between two specified dates.

Overload

Parameters

Return value

A Number value.

Examples

DatePart(intervalType, inputDateTime, firstDayOfWeek, firstWeekOfYear)

Returns a number that specifies a given part of a given date.

Overloads

Parameters

Examples

Notes:

DateTimeTo2000(Datetime, Number)

Parameters

Return value

A Date field with a four digit year.

Examples

DateTimeToDate(Datetime a)

This function evaluates the specified TimeStamp value and returns only the date.

Parameter

a - A TimeStamp value.

Return value

The return value is a date.

Example

If the current date is Oct. 17, 1999 and the current time is 12:27:15, the return value of the following statement is 10/17/99.

DateTimeToDate(CurrentDateTime())

DateTimeToSeconds(Datetime a)

This function evaluates the specified value and converts the time to the number of seconds from 00:00:00 to the specified time.

Parameter

a - A TimeStamp value.

Return value

The return value is an Integer.

Example

If the current date is Oct. 17, 1999 and the current time is 12:27:15, the return value of the following statement is 44835.

DateTimeToSeconds(CurrentDateTime())

DateTimeToTime(Datetime a)

This function evaluates the specified TimeStamp value and returns only the time.

Parameter

a - A TimeStamp value.

Return value

The return value is a Time.

Example

If the current date is Oct. 17, 1999 and the current time is 12:27:15, the return value of the following statement is 12:27:15.

DateTimeToTime(CurrentDateTime())

DateTo2000(date, number)

Parameters

Return value

A Date field with a four digit year.

Examples

Notes:

Day()

Day()

This function returns the day of the month.

Return value

The return value is an Integer.

Example

If today is Oct. 17, 1999, the return value of the following statement is 17.

Day()

Day(Date a)

This function extracts the day portion of a specified Date value.

Parameter

a - A Date value.

Return value

The return value is an Integer.

Example

If the current date is Oct. 17, 1999, the return value of the following statement is 17.

Day(CurrentDate())

Day(Datetime a)

This function extracts the day portion of a specified Date value.

Parameter

a - A TimeStamp value.

Return value

The return value is an Integer.

Example

If the current date is Oct. 17, 1999 and the current time is 12:27:15, the return value of the following statement is 17.

Day(CurrentDateTime())

DayOfWeek()

DayOfWeek()

This function returns the day of the week.

Return value

The return value is an Integer.

Example

If today is Thursday, the return value of the following statement is 5.

DayOfWeek()

DayOfWeek(Date a)

This function extracts the day portion of a specified Date value, and indicates which day of the week.

Parameter

a - A Date value.

Return value

The return value is an Integer.

Example

If today is Oct. 17, 1999 and today is Sunday, the return value of the following statement is 1.

DayOfWeek(CurrentDate())

DayOfWeek(Datetime a)

This function extracts the day portion of a specified Date value, and indicates which day of the week.

Parameter

a - A TimeStamp value.

Return value

The return value is an Integer.

Example

If the current date is Oct. 17, 1999, current time is 12:27:15 and today is Sunday, the return value of the following statement is 1 (Sunday is the first day of the week).

DayOfWeek(CurrentDateTime())

DayOfYear()

This function returns the day of the year.

Return value

The return value is an Integer.

Example

If today is Oct. 17, 1999, the return value of the following statement is 290.

DayOfYear()

DTSTo2000(DateString, Number)

Parameters

Return value

A DateTime string with a four digit year.

Examples

DTSToSeconds(string)

Evaluates the specified string, and converts the Time value to the number of seconds from 00:00:00 (12:00 midnight) to the specified time.

Parameter

string - A String which includes a Date and a Time value.

Return value

Whole number.

Example

DTSToSeconds("2004/06/11 13:42:15")- Returns 49335.

FirstDayOfMonth(Date a)

This function returns the first day of the month.

Parameter

a - A Date value.

Return value

The return value is a Date.

Example

If the current date is 11/24/2008, the return value of the following statement is 11/01/2008.

FirstDayOfMonth(CurrentDate())

FirstDayOfQuarter(Date a)

This function returns the first day of the quarter.

Parameter

a - A Date value.

Return value

The return value is a Date.

Example

The return value of the following statement is 10/01/2008.

FirstDayOfQuarter(ToDate("11/24/2008"))

FirstDayOfWeek(Date a)

This function returns the first day of the week.

Parameter

a - A Date value.

Return value

The return value is a Date.

Example

The return value of the following statement is 11/23/2008.

FirstDayOfWeek(ToDate("11/24/2008"))

FirstDayOfYear(Date a)

This function returns the first day of the year.

Parameter

a - A Date value.

Return value

The return value is a Date.

Example

The return value of the following statement is 01/01/2008.

FirstDayOfYear(ToDate("11/24/2008"))

FirstSundayOfMonth(Date a)

This function returns the first Sunday of the month.

Parameter

a - A Date value.

Return value

The return value is a Date.

Example

The return value of the following statement is 11/02/2008.

FirstSundayOfMonth(ToDate("11/24/2008"))

FirstSundayOfYear(Date a)

This function returns the first Sunday of the year.

Parameter

a - A Date value.

Return value

The return value is a Date.

Example

The return value of the following statement is 01/06/2008.

FirstSundayOfYear(ToDate("11/24/2008"))

ForEachDay(Datetime a)

This function returns the Date portion of a specified DateTime value.

Parameter

a - A DateTime value.

Return value

The return value is a Date.

Example

If the DateTime is Dec.25,1999 20:12:50, the return value of the following statement is 12/25/99.

ForEachDay(ToDateTime(1999, 12, 25, 20, 12, 50))

ForEachHalfMonth(DateTime a)

If the day in the DateTime is less than 15, this function will return to the first day of the month. If the day is more than 15, it will return to the 16th day of the month.

Parameter

a - A DateTime value.

Return value

The return value is a date.

Examples

ForEachHalfYear(DateTime a)

If the month of a specified DateTime is before July, the function will return to Jan.1 of the year. Otherwise the function will return to July 1 of the year.

Parameter

a - A DateTime value.

Return value

The return value is a date.

Examples:

ForEachMonth(DateTime a)

This function returns the first day of the month in a specified DateTime value.

Parameter

a - A DateTime value.

Return value

The return value is a date.

Example

If the datetime is Sep.24,1999 12:05:56, the return value of the following statement is 09/01/99.

ForEachMonth(ToDateTime(1999,9,24,12,5,56))

ForEachQuarter(DateTime a)

If the month of a specified DateTime is before April, the function will return to Jan. 1 of the year. If the month is after April but before July, the function will return to April. 1 of the year. If the month is after July but before October, the function will return to July. 1 of the year. If the month is after October, the function will return to Oct.1 of the year.

Parameter

a - A DateTime value.

Return value

The return value is a date.

Examples

ForEachWeek(DateTime a)

This function returns the Date of the first day of the week.

Parameter

a - A DateTime value.

Return value

The return value is a Date.

Example

If the DateTime is Oct. 15,1999 5:15:20 and it is Friday, the return value of the following statement is 10/10/99.

ForEachWeek(ToDateTime(1999, 10, 15, 5, 15, 20))

ForEachYear(DateTime a)

This function will return to Jan.1 of the year.

Parameter

a - A DateTime value.

Return value

The return value is a date.

Example

If the datetime is Aug. 28,1999 10:10:25, the return value of the following statement is 01/01/99.

ForEachYear(ToDateTime(1999, 8, 28, 10, 10, 25))

Hour()

Hour()

This function extracts the hour portion of the current time.

Return value

The return value is an integer.

Example

If the current time is 8:15:30, the return value of the following statement is 8.

Hour()

Hour(DateTime a)

This function extracts the hour portion of a specified TimeStamp value.

Parameter

a - A TimeStamp value.

Return value

The return value is an Integer.

Example

Suppose the datetime is Oct. 15,1999 9:35:22. The return value of the following statement is 9.

Hour(ToDateTime(1999,10,15,9,35,22))

Hour(Time a)

This function extracts the hour portion of a specified Time value.

Parameter

a - A Time value.

Return value

The return value is an Integer.

Example

Suppose the time is 10:05:26. The return value of the following statement is 10.

Hour(ToTime(10, 5, 26))

IsDate(number or string)

Returns true if the given String or Number value can be converted to a valid Date and returns false otherwise. A valid Date is any Date between 100-1-1 and 9999-12-31.

Parameters

Return value

Boolean value, True or False.

Examples

IsDateTime(number/string)

Returns True if the given Number or String value can be converted to a valid DateTime, and returns False otherwise.

Parameters

Return value

Boolean value, true or false.

Examples

IsTime(number or string)

Returns True if the given Number or String value can be converted to a valid Time, and returns False if otherwise.

Parameters

Return value

Boolean value, true or false.

Examples

Minute()

Minute()

This function returns the minute portion of the current time.

Return value

The return value is an Integer.

Example

If the current time is 12:41:27, the return value of the following statement is 41.

Minute()

Minute(DateTime a)

This function returns the minute portion of a specified DateTime.

Parameter

a - A DateTime value.

Return value

The return value is an Integer.

Example

Suppose the datetime is Sep. 21,1997 12:20:25. The return value of the following statement is 20.

Minute(ToDateTime(1997, 9, 21, 12, 20, 25))

Minute(Time a)

This function returns the minute portion of a specified time.

Parameter

a - A Time value.

Return value

The return value is an Integer.

Example

Suppose the time is 8:30:27. The return value of the following statement is 30.

Minute(ToTime(8,30,27))

Month()

Month()

This function adding one returns the current month of the year.

Return value

The return value is an Integer, which ranges from 0 to 11.

Example

If the current month is September, the return value of the following statement is 8.

Month()

Month(Date a)

This function extracts the month portion from a specified Date value.

Parameter

a - A Date value.

Return value

The return value is an Integer.

Example

If the date is June 15,1999, the return value of the following statement is 6.

Month(ToDate(1999, 6, 15))

Month(DateTime a)

This function extracts the month portion from a specified TimeStamp value.

Parameter

a - A TimeStamp value.

Return value

The return value is an Integer.

Example

If the timestamp is July 15, 1999 10:10:10, the return value of the following statement is 7.

Month(ToDateTime(1999, 7, 15, 10, 10, 10))

MonthName(month, abbreviate)

Returns a String name for the specified month.

Parameters

Return value

String value.

Examples

Now()

This function prints the current time on a report.

Return value

The return value is a Time.

Example

If the current time is 1:24:35 in the afternoon, the return value of the following statement is 1:24:35 PM.

Now()

Quarter()

Returns the quarter for the given date (1 for the first quarter).

Overloads

Parameters

Return value

Return an integer (1, 2, 3, 4).

Examples

Second()

Second()

This function extracts the second portion of the current time.

Return value

The return value is an Integer.

Example

If the current time is 4:55:03, the return value of the following statement is 3.

Second()

Second(Datetime a)

This function extracts the second portion of a specified TimeStamp.

Parameter

a - A TimeStamp value.

Return value

The return value is an Integer.

Example

Suppose the datetime is Oct. 15,1999 10:15:17, the return value of the following statement is 17.

Second(ToDateTime(1999, 10, 15, 10, 15, 17 ))

Second(Time a)

This function extracts the second portion of a specified time.

Parameter

a - A Time value.

Return value

The return value is an Integer.

Example

Suppose the time is 7:51:59, the return value of the following statement is 59.

Second(ToTime(7, 51, 59))

SelectedDate()

Returns the date selected in the calendar or returns today() when no calendar is available for selecting a date.

There is a built-in expression Selected Date in Calendar calling this function. The expression can be found in the Name drop-down list of the Calendar dialog.

Return value

Date value.

Example

If the calendar date is March 12, 2012, the function returns 03/12/2012.

Timer()

Returns the number of seconds elapsed since midnight.

Return value

Number value.

Example

If the current time is 9:28:30am, the function returns 34110.

ToDate()

ToDate(number or string)

Uses the function IsDate(number or string) to check whether the number/string is right or not. If the number/string is right, the function returns a Date value between 100-1-1 and 9999-12-31. If the number/string is wrong, it returns null.

Parameters

Return value

Date value or null.

Examples

ToDate(Date a)

This function returns the specified date.

Parameter

a - A Date value.

Return value

The return value is a Date.

Example

Suppose the date is July 15, 1999, the return of the following statement is 07/15/99.

ToDate(ToDate(1999, 7, 15))

ToDate(Datetime a)

This function extracts the date portion of a specified DateTime.

Parameter

a - A TimeStamp value.

Return value

The return value is a Date value.

Example

Suppose the DateTime is July 15,1999 12:20:30, the return value of the following statement is 07/15/99.

ToDate(ToDateTime(1999, 7, 15, 12, 20, 30 ) )

ToDate(integer d)

This function converts the argument d (A milliseconds value represents the number of milliseconds that have passed since January 1, 1970 00:00:00.000 GMT) to a data value.

Parameter

d - A Milliseconds value, a DBField value or a parameter. It should be a long integer.

Return value

The return value is a Date.

Example

The return value of the following statement is 01/02/70.

ToDate(72000000)

Note: When the milliseconds value is larger than 2³², you should use DBField or parameter instead.

ToDate(integer y, Integer m, Integer d)

This function creates a Date value from arguments y, m and d.

Parameters

Return value

The return value is a Date.

Example

The return value of the following statement is 07/15/99.

ToDate(1999, 7, 15)

ToDateTime()

ToDateTime(number or string)

Use the function IsDateTime to check whether the number/string is right or not. If it is right, the function returns a DateTime value between 100-1-1 and 9999-12-31, otherwise the function returns null.

Parameters

Return value

DateTime value or null.

Examples

ToDateTime(date a)

Returns a DateTime value.

Parameter

a - A Date value that can be converted to a DateTime type value by this function.

Example

ToDateTime(ToDate("2004-2-23")) - Returns 2004-02-23 00:00:00.

ToDateTime(Date a, Time b)

This function creates a TimeStamp value from arguments a and b.

Parameters

Return value

The return value is a DateTime.

Example

Suppose the date is July 15,1999 and the time is 12:20:30, the return value of the following statement is 1999-07-15 12:20:30.

ToDateTime(ToDate(1999, 7, 15), ToTime(12, 20, 30))

ToDateTime(Integer d)

This function converts the argument d (A milliseconds value represents the number of milliseconds that have passed since January 1, 1970 00:00:00.000 GMT) to a TimeStamp value.

Parameter

d - A Milliseconds value, a DBField value or a parameter. It should be a long integer.

Return value

The return value is a DateTime.

Example

The return value of the following statement is 1970-01-02 04:00:00.

ToDateTime(72000000)

Notes:

ToDateTime(Integer y, Integer m, Integer d)

This function creates a TimeStamp value from arguments y, m and d.

Parameters

Return value

The return value is a DateTime.

Example

Suppose the date is July 15,1999, the return value of the following statement is 1999-07-15 12:00:00.

ToDateTime(1999, 7, 15)

Note: In this function, because you only input year, month and day, the system will output 12:00:00 as the default time. Also you can decide the display format by selecting the format from the Report Inspector. The format of the example above is yyyy-mm-dd hh:mm:ss.

ToDateTime(Integer y, Integer m, Integer d, Integer h, Integer i, Integer s)

This function creates a TimeStamp value from arguments y, m, d, h, i and s.

Parameters

Return value

The return value is a DateTime.

Example

Suppose the date is July 15,1999 and time is 8:05:30, the return value of the following statement is 1999-07-15 08:05:30.

ToDateTime(1999, 7, 15, 8, 5, 30)

Today()

This function returns the current date on a report.

Return value

The return value is a date.

Example

If the current date is Oct. 11, 1999, the return value of the following statement is 10/11/99.

Today()

ToTime()

ToTime(number or string)

Use the function IsTime to check whether the number/string is right or not. If the number/string is right, the function returns a Time value between 00:00:00 and 23:59:59, otherwise it will return null.

Parameters

Return value

Time value or null.

Examples

ToTime(DateTime a)

This function extracts the time portion from a specified TimeStamp value.

Parameter

a - A TimeStamp value.

Return value

The return value is a Time.

Example

Suppose the date is July 15,1999 and time is 10:55:45. The return value of the following statement is 10:55:45.

ToTime(ToDateTime(1999, 7, 15, 10, 55, 45 ))

ToTime(Integer t)

This function converts the argument t (A milliseconds value represents the number of milliseconds that have passed since January 1, 1970 00:00:00.000 GMT) to a Time value.

Parameter

t - A milliseconds value, a DBField value or a parameter. It should be a long integer.

Return value

The return value is a Time.

Example

The return value of the following statement is 08:00:01.

ToTime(1000)

Note: When the milliseconds value is larger than 2³², you should use DBField or parameter instead.

ToTime(Integer a, Integer b, Integer c)

This function creates a Time value from argument a, b and c.

Parameters

Return value

The return value is a Time.

Example

The return value of the following statement is 10:10:10.

ToTime(10, 10, 10)

WeekdayName(weekday, abbreviate, firstDayOfWeek)

WeekdayName returns a string indicating the name of the specified day of the week.

Overloads

Parameters

Return value

String value.

Examples

WeekFrom1970()

WeekFrom1970()

This function computes the total number of weeks from 1970 to the current date.

Return value

The return value is an Integer.

Example

If the current date is Oct. 13,1999, the return value of the following statement is 1554.

WeekFrom1970()

WeekFrom1970(Date, a)

This function computes the total number of weeks from 1970 to a specified date.

Parameter

a - A Date value.

Return value

The return value is an Integer.

Example

Suppose the date is July 8,1995. The return value of the following statement is 1331.

WeekFrom1970(ToDate(1995, 7, 8))

WeekFrom1970(DateTime, a)

This function computes the total number of weeks from 1970 to a specified DateTime.

Parameter

a - A DateTime value.

Return value

The return value is an Integer.

Example

Suppose the DateTime is Jan. 2,1988 10:40:50. The return value of the following statement is 940.

WeekFrom1970(ToDateTime(1988, 1, 2, 10, 40, 50))

WeekOfMonth()

WeekOfMonth()

This function returns the week of the current month.

Return value

The return value is an Integer.

Example

If the current date is Oct. 13,1999, the return value of the following statement is 3.

WeekOfMonth()

WeekOfMonth(Date a)

This function returns the week of the month of a specified date.

Parameter

a - A Date value.

Return value

The return value is an Integer.

Example

If the current date is Oct. 13,1999, the return value of the following statement is 3.

WeekOfMonth(ToDate(1999, 10, 13))

WeekOfMonth(DateTime a)

This function evaluates the week of the month of a specified TimeStamp.

Parameter

a - A TimeStamp value.

Return value

The return value is an Integer.

Example

Suppose the timestamp is Jan. 20,1999 10:40:50. The return value of the following statement is 4.

WeekOfMonth(ToDateTime(1999, 1, 20, 10, 40, 50))

WeekOfYear()

WeekOfYear()

This function returns the week of the current year.

Return value

The return value is an Integer.

Example

If the current date is Oct. 13,1999, the return value of the following statement is 42.

WeekOfYear()

WeekOfYear(Date a)

This function returns the week of the year in a specified date.

Parameter

a - A Data value.

Return value

The return value is an Integer.

Example

Suppose the date is July 8,1999. The return value of the following statement is 28.

WeekOfYear(ToDate(1999, 7, 8))

WeekOfYear(DateTime a)

This function returns the week of the year in a specified TimeStamp.

Parameter

a - A TimeStamp value.

Return value

The return value is an Integer.

Example

Suppose the timestamp is Feb. 4,1999 10:40:50. The return value of the following statement is 6.

WeekOfYear(ToDateTime(1999, 2, 4, 10, 40, 50))

Year()

Year()

This function returns the current year.

Return value

The return value is an Integer.

Example

If the current year is 1999, the return value of the following statement is 1999.

Year()

Year(Date a)

This function extracts the year portion of a specified date.

Parameter

a - A Data value.

Return value

The return value is an Integer.

Example

Suppose the date is Jan.15,1995. The return value of the following statement is 1995.

Year(ToDate(1995, 1, 15))

Year(DateTime a)

This function extracts the year portion of a specified TimeStamp value.

Parameter

a - A TimeStamp value.

Return value

The return value is an Integer.

Example

Suppose the timestamp is July 15,1988 10:40:50. The return value of the following statement is 1988.

Year(ToDateTime(1988, 7, 15, 10, 40, 50))