Dates and time

While dates and times are typically stored in columns that have an integer or decimal data type, they must be specified in a valid form to produce the expected results.

Most date and time values are represented in columns that have an integer data type. The date+time form, which contains both date and time information in one value, is unique, and it is represented by a column that has a decimal data type. Functions that operate on dates and times sample the input values in the column to make sure they are in the correct data type (integer or decimal). As long as the data type is correct, the function is performed on the input. Note that the values in the column are not checked to make sure that they are valid. If the values are not valid, the function returns an erroneous or illogical output value.

Dates

Values that contain date-related information can be specified in the following forms:


Form	Syntax	Example	Value
`date`	`YYYYMMDD`	11/5/2017	`20171105`
`month`	`YYYYMM`	11/2017	`201711`
`quarter`	`YYYYQ`	4Q2017	`20174`
`year`	`YYYY`	2017	`2017`

For dates to be considered valid and produce a correct result, they must appear in the format above, and the values for MM, DD, and Q must stay within the expected range. MM values range from 01-12, DD values from 01-31, and Q 1-4. If a function is expecting a date value as input and the value passed to the function is outside the valid range or supported format, the function returns an erroneous or illogical output.

Note: The values YYYYMMDD and YYYYMM are "tolerant of decoration", meaning that you can add slashes or other decoration. For example, 20171105 can be displayed as 11/5/2017.

Time

Values that contain time-related information can be specified in following format:


Form	Syntax	Example	Value
`time`	`HHMMSS`	12:46:18	`124618`

For times to be considered valid and produce a correct result, they must appear in the syntax above, and the values for each portion of HH, MM, and SS must stay within the expected range. HH values range from 00-23, and MM and SS values range from 00-59. If a function is expecting a time value as input and that value is outside the valid range or supported syntax, the function returns an erroneous or illogical output.

Date+Time

Values that contain a combination of date and time information can be specified in the following form:


Form	Syntax	Example	Value
`date+time`	`XXXX.YYYYYYYYYYY`	12/10/13_10:25:30	`-7691.56562470746`

The date+time input uses a convention similar to the Julian date, but uses 1/1/2035 as the epoch (or reference) point. Specifically, the value XXXX.YYYYYYYYYYY is a signed float representing the number of days before or after the epoch. The value is typically negative because the count uses the epoch as the starting point and counts backwards to the given date+time:

XXXX is the number of full days from the given date to the epoch
.YYYYYYYYYYY is the fraction of the current day remaining until midnight

So, for the above example, on 12/10/2013 at 10:25:30 there is still .56562470746 of the day remaining until midnight and 7691 full days until midnight on 1/1/2035.

Values in the date+time form are stored in columns that have a decimal data type.

You may need to convert another epoch format, such as Unix epoch (reference point 1/1/1970), to the date+time format (reference point 1/1/2035). To do this, you would divide the Unix epoch time by 86400 (since Unix epoch time is in seconds and date+time is in days). Then, you would offset the Unix epoch time by 23741 (the number of days difference in the epochs). The formula is date+time=unixtime/86400-23741.