Date()
|
Date() |
|
|
Working with Formulas > Functions Used in Formulas > Conversion Functions > Date()
The DATE() function is a versatile tool that can be used either as a way to input a date or as a way to convert a character type to a date type.
The first way to use DATE() is to input the values of the date in the order specified below, where "YYYY" specifies year, "MM" specifies month and "DD" specifies day. The second way to use DATE() is to input a character expression as the parameter for the function. DATE() converts the character expression to a date expression. The character expression can specify the date in the following formats:
MM/DD/YYYY YYYY/DD/MM
For conversion mapping functions, you may also use the following formats:
DDMMYYYY YYYYDDMM MMYYYYDD DDYYYYMM
Also, you can use dashes instead of slashes to separate the month, day, and year parts of the string such as:
MM-DD-YYYY or YYYY-MM-DD
As an example of this dual usage, if you had a date field called "field1" and wish to filter your data so you only see dates after February 1, 2007, you could use either of the following formulas:
Field1 > DATE(2007,2,1) Field1 > DATE("02/01/2007", "MM/DD/YYYY")
The first enables you to input the date, the second takes the additional step to convert the character string to the date format.
Additionally, there are two special date conversion cases worth mentioning: Julian date format and Unix timestamp formats. A Julian date is the number of days since January 1, 4713 BC GMT. To convert a Julian date to the current date, you can simply use DATE(0,0,0) + <number>, where <number> is the Julian date. For for example, if the number appears as "2454917", the formula in Strata would be:
DATE(0,0,0) + 2454917 [the result would be March, 26, 2009]
A Unix timestamp is the number of seconds since 1970. To convert a Unix timestamp to the current date, use DATE(<number>), where <number> is the number of milliseconds since 1970. If the timestamp is given in seconds, multiply by 1000: DATE(<number>*1000). So, for example, if you the number appears as "1237657172", the formula in Strata would be:
DATE(1238088021 * 1000) [the result would be March, 26, 2009]
Function Format
DATE(year, month, day [, hour, minute, second]) DATE(string) DATE(string, conversion mapping string)
Return Value
DATE() returns a date
Examples
DATE(2007,12,25) = the date, December 25, 2007
DATE(2007,12,25,8,15,30) = the date, December 25, 2007 at 8:15:30 A.M.
DATE("07/04/2007") = the date, July 4, 2007
DATE("2007-10-22") = the date, October 22, 2007
DATE(0,0,0) + 2454917 = the date, March, 26, 2009
DATE(1238088021 * 1000) = the date, March, 26, 2009
DATE("20071122","YYYYMMDD") = 11/22/2007
DATE("2007-22-11","YYYYDDMM") = 11/22/2007
DATE("11222007","MM/DD/YYYY") = 11/22/2007
DATE("11/22/2007", "MMDDYYYY") = 11/22/2007
|