- Getting Started
- Administration Guide
-
User Guide
- An Introduction to Wyn Enterprise
- Document Portal for End Users
- Data Governance and Modeling
- Working with Resources
- Working with Reports
- Working with Dashboards
- View and Manage Documents
- Understanding Wyn Analytical Expressions
- Section 508 Compliance
- Developer Guide
Date and Time Functions
These functions are very similar to the date and time functions of Microsoft Excel. You can use these functions to extract dates, calculate the difference between two dates, find the current date and time, and much more.
Following is the list of date and time functions supported in Wyn Enterprise:
DATE
Description
The DATE function returns the specified date in the datetime format.
Syntax
DATE(<year>,<month>,<day>) Parameters
Term | Description |
|---|---|
year | A four-digit number representing a year. |
month | A number ranging from 1 to 12 represents the month of the year. |
day | A number ranging from 1 to 31 represents the day of the year. |
Return Value
A specified date (datetime).
Example
DATE(2012,12,12) This example will return the date as December 12, 2012.
DATEDIFF
Description
The DATEDIFF function returns the number of units (unit specified in Interval) between the input two dates.
Syntax
DATEDIFF(<start_date>, <end_date>, <interval>)Parameters
Term | Description |
|---|---|
start_date | A date in datetime format representing the start date. |
end_date | A date in datetime format representing the start date. |
interval | A unit to use for comparing dates. The possible values are SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR. |
Return Value
The count of interval boundaries crossed between two dates.
Example
DATEDIFF('Sale'[Order Date], TODAY(), DAY)In this example, the formula will return the count of intervals in days between Sale Order Date and the Current date.
DAY
Description
The DAY function returns a number from 1 to 31 representing the day of the month.
Syntax
DAY(<date>)Parameters
Term | Description |
|---|---|
date | A date in datetime format. |
Return Value
An integer value ranging from 1 to 31.
Example
DAY('Sale'[Sale Date])In this example, the formula returns the day from the date in the column, [Sale Date].
HOUR
Description
The HOUR function returns the hour as a number from 0 (12:00 A.M.) to 23 (11:00 P.M.).
Syntax
HOUR(<datetime>)Parameters
Term | Description |
|---|---|
datetime | A datetime value, such as 16:48:00 or 4:48 PM. |
Return Value
An integer value ranging from 0 to 23.
Example 1
HOUR('Sale'[Sale DateTime])This example returns the hour from the Sale DateTime column of a Sale table.
Example 2
HOUR("March 3, 2023 5:10 PM") This example returns 17, which means the hour corresponding to 5 PM in a 24-hour clock. The text value is automatically parsed and converted to a date/time value.
MINUTE
Description
The MINUTE function returns a number from 0 to 59 representing the minute.
Syntax
MINUTE(<datetime>)Parameters
Term | Description |
|---|---|
datetime | A datetime value or text in an accepted time format, such as 16:48:00 or 4:48 PM. |
Return Value
An integer value ranging from 0 to 59.
Example 1
MINUTE('Sale'[Sale DateTime])This example returns the minute from the value stored in the Sale DateTime column of the Sale table.
Example 2
MINUTE("April 25, 2023 12:40 PM") This example will return 40, which is the number of minutes in the time 12:40 PM.
MONTH
Description
The MONTH function returns a number from 1 (January) to 12 (December), representing the month.
Syntax
MONTH(<date>)Parameters
Term | Description |
|---|---|
date | A date in datetime or text format. |
Return Value
An integer value ranging from 1 to 12.
Example 1
MONTH('Sale'[Sale Datetime])This example will return the month from the Sale DateTime column in the Sale table.
Example 2
MONTH("April 12, 2023 5:49 PM") This example will return 4, which is the integer corresponding to April, the month in the date argument.
NOW
Description
The NOW function returns the current date and time in datetime format. The NOW function is useful when you need to display the current date and time on a worksheet or calculate a value based on the current date and time, and have that value updated each time you open the worksheet.
Syntax
NOW() Return Value
A date (datetime).
Example
NOW()This example will return the current date and time in datetime format.
QUARTER
Description
The QUARTER function returns a number ranging from 1 (January-March) to 4 (October-December) representing the quarter.
Syntax
QUARTER(<date>)Parameters
Term | Description |
|---|---|
date | A date. |
Return Value
An integer value ranging from 1 to 4.
Example
QUARTER('Sale'[Sale Date])This example returns the quarters in the Sale Date column in the Sale table.
SECOND
Description
The SECOND function returns a number from 0 to 59 representing the second.
Syntax
SECOND(<datetime>)Parameters
Term | Description |
|---|---|
datetime | A time in datetime format. |
Return Value
An integer value ranging from 0 to 59.
Example 1
SECOND(now())This example will return the current time second value.
Example 2
SECOND("May 10, 2023 11:43:10")
This example will return 10, which is the number of seconds in the time represented by the value, May 10, 2023 11:43:10.
TODAY
Description
The TODAY function returns the current date in datetime format.
Syntax
TODAY() Return Value
A date (datetime).
Example
TODAY()This example will return the current date in DateTime format.
Remarks
The NOW function is also similar but it returns the exact time, whereas the TODAY function returns the time value 00:00:00 for all the dates.
WEEKDAY
Description
The WEEKDAY function returns a number identifying the day of the week of a date. The value ranges from 1 (Sunday) to 7 (Saturday).
Syntax
WEEKDAY(<datetime>)Parameters
Term | Description |
|---|---|
datetime | A time in datetime format. |
Return Value
An integer value ranging from 1 to 7.
Example
WEEKDAY(now())This example will return the day of the week for the current date.
WEEKNUM
Description
The WEEKNUM function returns the week number for the given date according to the return_type value. The week number indicates where the week falls numerically within a year.
Syntax
WEEKNUM(<datetime>,<returnType>)Parameters
Term | Description |
|---|---|
datetime | A time in datetime format. |
returnType | An integer number (0 or 1) that determines on which day the week begins. • 0 (Sunday): Western traditional • 1(Monday): ISO-8601 |
Return Value
An integer.
Example
WEEKNUM(now(),1)This example will return the week number from the current date. This calculation assumes the week begins on Monday.
YEAR
Description
The YEAR function returns the year of a date as a four-digit integer in the range 1900-9999.
Syntax
YEAR(<date>)Parameters
Term | Description |
|---|---|
date | A date in datetime or text format, containing the year you want to find. |
Return Value
An integer in the range 1900-9999.
Example
Year('Sale'[Sale Datetime])This example will return the year for the Sale Datetime in the Sale table.
UTCNOW
Description
UTCNOW function returns the current UTC date and time.
Syntax
UTCNOW() Return Value
A (datetime)
Example
UTCNOW()This example will return the current UTC date and time in datetime format and work with time zone-independent values and use Coordinated Universal Time (UTC).
UTCTODAY
Description
UTCTODAY function returns the current UTC date.
Syntax
UTCTODAY() Return Value
A date.
Example
UTCTODAY()This example will return the current UTC date and work with time zone-independent values and use Coordinated Universal Time (UTC).