Differences

This shows you the differences between two versions of the page.

--- dev:sql:functions:calc_date [2010/07/27 17:37]
els
+++ dev:sql:functions:calc_date [2016/06/28 22:38] (current)
@@ Line 1: / Line 1: @@
 {{page>:top_add&nofooter&noeditbtn}}
-{{page>:sql_bar&nofooter&noeditbtn}}
+====== Development: Omnidex SQL ======
-====== Omnidex SQL Function: $CALC_DATE ======
-===== Description =====
+===== SQL Functions: $CALC_DATE ======
+==== Description ====
 The $CALC_DATE function performs arithmetic on a date expression, returning a new date value.  The arithmetic is performed using the units declared in the function, or using DAYS if not specified.
 This function returns the same datatype as the passed date expression.
-===== Syntax =====
+==== Syntax ====
-  $CALC_DATE(date_expression, numeric_expression [, date_token] )
+  $CALC_DATE( date_expression, numeric_expression [ , date_token ] )
 == date_expression ==
@@ Line 21: / Line 23: @@
 If this function is to return a date in the future, the numeric expression should return a positive value.  If this function is to return a date in the past, the numeric expression should return a negative value.
-== date_tokens ==
+== date_token ==
 Optional. One of the following tokens representing the unit for the date arithmetic.
@@ Line 33: / Line 35: @@
-===== Example =====
+==== Example ====
-==== Example 1: EXTRACT of MONTH ====
+=== Example 1: $CALC_DATE adding 18 years ===
 <code>
-> select birthdate, extract(month from birthdate) mon from individuals;
+> select birthdate, $calc_date(birthdate, 18, 'YY') bday_plus_18 from individuals;
-BIRTHDATE     MON
+BIRTHDATE     BDAY_PLUS_18
-------------  -----------
+------------  ------------
--07-14            7
+-07-14    1956-07-14
--11-22           11
+-11-22    1975-11-22
--07-28            7
+-07-28    1978-07-28
--08-22            8
+-08-22    1947-08-22
--07-09            7
+-07-09    1951-07-09
--08-01            8
+-08-01    1962-08-01
--09-14            9
+-09-14    2022-09-14
-</code>
-==== Example 2: EXTRACT with format string ====
-<code>
-> select birthdate, extract ('MMM D, YYYY' from birthdate) bday from individuals;
-BIRTHDATE     BDAY
-------------  -------------------
--07-14  July 14, 1938
--11-22  November 22, 1957
--07-28  July 28, 1960
--08-22  August 22, 1929
--07-09  July 9, 1933
--08-01  August 1, 1944
--09-14  September 14, 2004
 </code>
@@ Line 69: / Line 56: @@
 See also:
 {{page>dev:sql:functions:see_also_dates&noheader&nofooter&noeditbtn}}
 {{page>:bottom_add&nofooter&noeditbtn}}
-======
-{{page>:top_add&nofooter&noeditbtn}}
-<html><div align="center"><span style="color:red">DRAFT</span></div></html>
-====== Omnidex SQL Function: $CALC_DATE ======
-{{page>:sql_bar&nofooter&noeditbtn}}
-===== Description =====
-$CALC_DATE calculates a new date from a specified date or column_date using a numeric positive or negative offset.  The offset defaults to days but can be changed by using the date_unit optional argument.
-===== Syntax =====
-Result = $calc_date( <date_column | 'date_string' >, offset_expression [, date_unit [, options])
-Returns a floating point value.
-===== Discussion =====
-== <date_column_spec | 'date_string' > ==
-An expression resulting in date-class or string-class datatype.
-== offset_expression ==
-An expression resulting in a numeric-class (integer or floating point) value.
-Negative values supported.
-== date_unit ==
-A string containing unit abbreviation that tell the $calc_date function to add or subtract the number of date_units.
-Valid date_unit options:
-^ Date_unit ^ Description ^
-| YEAR | Year |
-| MONTH | Month |
-| DAY | Day |
-| HOUR | Hour |
-| MINUTE | Minute |
-| SECOND | Second |
-| A | Lowercase am/pm indicator |
-| AA | Uppercase AM/PM indicator |
-| D | Non-zero-filled day-of-month (1, 2, 3) |
-| 0D | Zero-filled day-of-month (01, 02, 03) |
-| DD | Day of year (44) |
-| 0DD | Zero-filled day of year (044) |
-| F | Non-zero-filled fraction of a second (1, 2) |
-| 0F | Zero-filled fraction of a second (01, 02) |
-| H | 12-hour, non-zero-filled hour of day (12, 1) |
-| 0H | 12-hour, zero-filled hour of day (12, 01) |
-| HH | 24-hour, non-zero-filled hour of day (24, 1) |
-| 0HH | 24-hour, zero-filled hour of day (24, 01) |
-| J | Non-zero-filled Julian date (1, 2) |
-| 0J | Zero-filled Julian date (01, 02) |
-| M | Non-zero-filled month number (1-January, 2-February) |
-| 0M | Zero-filled day-of-month number (01-January, 02-February) |
-| MM | Three-character month abbreviation (Jan, Feb) |
-| MMM | Fully spelled month (January, February) |
-| N | Non-zero-filled minute of hour (1, 2) |
-| 0N | Zero-filled minute of hour (01, 02) |
-| Q | Quarter of the year (1) |
-| QQ | Quarter of the year abbreviation (Q1) |
-| QQQ | Quarter of the year name (First Quarter) |
-| S | Non-zero-filled second of minute (1, 2) |
-| 0S | Zero-filled second of minute (01, 02) |
-| W | Day of the week number (1-Sunday, 2-Monday) |
-| WW | Three-character day-of-week abbreviation (Sun, Mon) |
-| WWW | Fully spelled day-of-week (Sunday, Monday) |
-| YY | Two-digit year (99, 00) |
-| YYYY | Four-digit year (1999, 2000) |
-== Options ==
-There are no options currently for this function.
-===== Example =====
-==== Example 1 ====
-<code>
-> select $calc_date('20090101', -5) from $omnidex
-$CALC_DATE('20090101',
-----------------------
--12-27
-</code>
-==== Example 2 ====
-<code>
-> select $calc_date('20090101', -5,YEAR) from $omnidex
-$CALC_DATE('20090101',
-----------------------
--01-01
-</code>
-{{page>:bottom_add&nofooter&noeditbtn}}

Back to top

dev/sql/functions/calc_date.1280252269.txt.gz · Last modified: 2016/06/28 22:38 (external edit)

Sitemap Recent Changes