Difference between revisions of "Special Handling of Date Values"

 
(12 intermediate revisions by 4 users not shown)
Line 1: Line 1:
Analytica includes group of [[Date Functions]] specifically designed to create and manipulate date values. But there are also some common functions which, although they are typically applied to standard numerical values, include optional parameters to handle dates. This page links to all functions that handle dates.
+
[[Category:Date and Time Functions]]
 +
[[Category:Doc Status C]] <!-- For Lumina use, do not change -->
 +
 
 +
__TOC__
 +
 
 +
Analytica includes a set of [[Date Functions]] that let you create and manipulate date values and time values. Date values represent the number of days since Jan 1, 1900. A date value can also represent hours, minutes, seconds, and smaller units as a fraction of a day (digits after the decimal point). For example, 
 +
:<code>DateValue 0 is 1-Jan-1900</code>
 +
:<code>DateValue 1 is 2-Jan-1900</code>
 +
:<code>DateValue 1.5 2-Jan-1900:12:00:00</code>
 +
 
 +
Common rounding functions and [[Sequence]] take an optional «dateUnit» parameter to work with dates:
 +
* [[Floor]](x, dateUnit): Rounds «x» down to the nearest integer equal or below «x», if «dateUnit» is omitted. Or you can specify «dateUni»t to round down to the nearest (preceding or same date) that starts the year (Jan 1), quarter, or other time unit as shown below.
 +
* [[Ceil]](x, dateUnit): Rounds «x» up to the nearest integer equal or above «x», if «dateUnit» is omitted. Or you can specify a «dateUnit» to round up to the nearest specified time unit.
 +
* [[Round]](x, dateUnit): Rounds «x» down to the nearest integer to «x», if «dateUnit» is omitted. Or you can specify «dateUnit» to the units to which you
 +
* [[Sequence]](startD, endD, dateUnit):  Generates a sequence of dates (or date-time) from «startD» to «endD», using increments of «dateUnit».
 +
* [[DateAdd]](d, offset, unit): Returns a date-time by adding «offset» (in specified «unit»s) to date «d».
 +
 
 +
 
 +
«dateUnit» can be any of these text values:
 +
:<code>'Y'</code> for Year
 +
:<code>'Q'</code> for Quarter
 +
:<code>'M'</code> for Month
 +
:<code>'WD'</code> for Weekday
 +
:<code>'D'</code> for Day (default if «dateUnit» is omitted)
 +
:<code>'h'</code> for Hour
 +
:<code>'m'</code> for minute
 +
: <code>'s'</code> for second
 +
Note that ''Month'' is upper case <code>'M'</code> and ''minute'' is lower case <code>'m'</code>.
 +
 
 +
;Examples:
 +
Round up to the next year:
 +
:<code>Ceil(MakeDate(2014, 12,17), dateUnit: 'Y') &rarr; 1-Jan-2015</code>
 +
 
 +
Round to the nearest top of the minute:
 +
:<code>Round(Today(withTime: 1), dateUnit: 'm') &rarr; 17-Dec-2014 11:32:00 AM</code>
 +
 
 +
List the next 5 Groundhog Days:
 +
:<code>Sequence(MakeDate(2015, 2, 2), MakeDate(2019, 2, 2), dateUnit: 'Y')  &rarr; </code>
 +
:<code>[2-Feb-2015, 2-Feb-2016, 2-Feb-2017, 2-Feb-2018, 2-Feb-2019]</code>
 +
or equivalently,
 +
:<code>DateAdd(2-Feb-2015, 0..4, unit:'Y') &rarr;</code>
 +
:<code>[2-Feb-2015, 2-Feb-2016, 2-Feb-2017, 2-Feb-2018, 2-Feb-2019]</code>
 +
 
 +
== Creating Dates ==
 +
It is possible to create a date value without help from any function. A value will be recognized as a date if it follows the [[syntax]] <code>dd-EEE-yyyy </code>
 +
 
 +
Where:
 +
:<code>dd</code> is one or two digits for day of month
 +
:<code>EEE</code> is a three character text code for the month in the default language set in the OS. (English in the example below)
 +
:<code>yyyy</code> is four digits for the year
 +
 
 +
;Example: <code>04-DEC-2015</code>
 +
 
 +
The bare date syntax must be used on its own. It cannot be embedded within an expression.
 +
 
 +
To explicity define a date you can use either [[ParseDate]]() or [[MakeDate]]().
 +
 
 +
== Importing Dates ==
 +
[[DbQuery]]() and [[SpreadsheetRange]]() can import date values into Analytica. Both recognize date type values in the source data.
 +
 
 +
By default, [[DbQuery]] does not convert these to dates when importing but you can change this preference using the «datesAsText» parameter. More details [[DbQuery#Date_and_Time_data|here]].
 +
 
 +
[[SpreadsheetRange]]()  always preserves date values when they are imported. There are no optional parameters to think about in this case.
 +
 
 +
== Dedicated Date Functions ==
 +
The section above summarized special handling of dates using multi-purpose functions. Dedicated date functions are documented on the [[Date Functions]] page.
 +
 
 +
==See Also==
 +
* [[Date and Time Values]]
 +
* [[Date formats]]
 +
* [[Date Functions]]
 +
* [[YearFrac]]
 +
* [[ParseDate]]()
 +
* [[MakeDate]]
 +
* [[Sequence]]()
 +
* [[SpreadsheetRange]]()
 +
* [[DbQuery]]

Latest revision as of 21:15, 24 March 2016


Analytica includes a set of Date Functions that let you create and manipulate date values and time values. Date values represent the number of days since Jan 1, 1900. A date value can also represent hours, minutes, seconds, and smaller units as a fraction of a day (digits after the decimal point). For example,

DateValue 0 is 1-Jan-1900
DateValue 1 is 2-Jan-1900
DateValue 1.5 2-Jan-1900:12:00:00

Common rounding functions and Sequence take an optional «dateUnit» parameter to work with dates:

  • Floor(x, dateUnit): Rounds «x» down to the nearest integer equal or below «x», if «dateUnit» is omitted. Or you can specify «dateUni»t to round down to the nearest (preceding or same date) that starts the year (Jan 1), quarter, or other time unit as shown below.
  • Ceil(x, dateUnit): Rounds «x» up to the nearest integer equal or above «x», if «dateUnit» is omitted. Or you can specify a «dateUnit» to round up to the nearest specified time unit.
  • Round(x, dateUnit): Rounds «x» down to the nearest integer to «x», if «dateUnit» is omitted. Or you can specify «dateUnit» to the units to which you
  • Sequence(startD, endD, dateUnit): Generates a sequence of dates (or date-time) from «startD» to «endD», using increments of «dateUnit».
  • DateAdd(d, offset, unit): Returns a date-time by adding «offset» (in specified «unit»s) to date «d».


«dateUnit» can be any of these text values:

'Y' for Year
'Q' for Quarter
'M' for Month
'WD' for Weekday
'D' for Day (default if «dateUnit» is omitted)
'h' for Hour
'm' for minute
's' for second

Note that Month is upper case 'M' and minute is lower case 'm'.

Examples

Round up to the next year:

Ceil(MakeDate(2014, 12,17), dateUnit: 'Y') → 1-Jan-2015

Round to the nearest top of the minute:

Round(Today(withTime: 1), dateUnit: 'm') → 17-Dec-2014 11:32:00 AM

List the next 5 Groundhog Days:

Sequence(MakeDate(2015, 2, 2), MakeDate(2019, 2, 2), dateUnit: 'Y') →
[2-Feb-2015, 2-Feb-2016, 2-Feb-2017, 2-Feb-2018, 2-Feb-2019]

or equivalently,

DateAdd(2-Feb-2015, 0..4, unit:'Y') →
[2-Feb-2015, 2-Feb-2016, 2-Feb-2017, 2-Feb-2018, 2-Feb-2019]

Creating Dates

It is possible to create a date value without help from any function. A value will be recognized as a date if it follows the syntax dd-EEE-yyyy

Where:

dd is one or two digits for day of month
EEE is a three character text code for the month in the default language set in the OS. (English in the example below)
yyyy is four digits for the year
Example
04-DEC-2015

The bare date syntax must be used on its own. It cannot be embedded within an expression.

To explicity define a date you can use either ParseDate() or MakeDate().

Importing Dates

DbQuery() and SpreadsheetRange() can import date values into Analytica. Both recognize date type values in the source data.

By default, DbQuery does not convert these to dates when importing but you can change this preference using the «datesAsText» parameter. More details here.

SpreadsheetRange() always preserves date values when they are imported. There are no optional parameters to think about in this case.

Dedicated Date Functions

The section above summarized special handling of dates using multi-purpose functions. Dedicated date functions are documented on the Date Functions page.

See Also

Comments


You are not allowed to post comments.