Home   Back   Next  

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:





For conversion mapping functions, you may also use the following formats:







Also, you can use dashes instead of slashes to separate the month, day, and year parts of the string such as:




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, conversion mapping string)



Return Value


DATE() returns a date





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