Differences

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

--- v5:reference:connection:offsetdate [2016/01/17 01:17] – ↷ Page moved from v5:reference:offsetdate to v5:reference:connection:offsetdate mnewnham
+++ v5:reference:connection:offsetdate [2025/08/20 05:04] (current) – mnewnham
@@ Line 1: / Line 1: @@
 ====== offsetDate ======
 ~~NOTOC~~
-<WRAP important>
-This function is only available in a limited number of drivers. See the driver documentation for information
-</WRAP>
 <WRAP right box>
 == Syntax ==
-  string offsetDate(
+  string|bool ADOConnection::offsetDate(
-         int $offset
+         string|float $offset
-         optional float $baseDate
+         optional string $isoDate
   )
 </WRAP>
-====== Description ======
-The function ''offsetDate()'' returns a string with the native SQL functions to calculate future and past dates based on ''$baseDate'' in a portable fashion. If ''$baseDate'' is not defined, then the current date (at 12 midnight) is used. Returns the SQL string that performs the calculation when passed to Execute().
-For example, in Oracle, to find the date and time that is 2.5 days from today, you can use:
+==== Note ====
+Passing a decimal offset less than one without
+specifying a base date will always return the
+current date because the calculation time
+basis is always the beginning of the current date
+===== Description =====
+The function ''offsetDate()'' returns a string with the native SQL functions to calculate future and past dates based on ''$isoDate'' in a portable fashion. If ''$isoDate'' is not defined, then the current date (at 12 midnight) is used.
+The function returns the SQL string that performs the calculation when passed to ''Execute()''. The value returned when the statement is executed is an //**ISO Date**//.
+The function returns false if invalid parameters are provided.
+===== Inputs =====
+The $offset parameter can be presented in 2 ways:
+  * As a decimal indicating the number of days where the decimal portion is the fraction of a day (e.g. 0.5 = 12 hours)
+  * As string in the format "float/24" where the floating point number is the number of hours
+The $isoDate can be:
+   * A basis date in ISO format including a time portion, if a fractional calculation is required
+   * An SQL statement that produces an ISO date
+   * An SQL column if the method is to be used to calculate date offsets based on table values
+   * skipped or false for today
+===== Example =====
+In Oracle, to find the date and time that is 2.5 days from today, you can use:
 <code php>
 /*
@@ Line 25: / Line 45: @@
  */
-/*
+/*===
  *  get date and time that is 60 hours from current date and time
  */
-$fld = $conn->OffsetDate(2.5, $conn->sysTimeStamp);
+$fld = $conn->OffsetDate("60/24", $conn->sysTimeStamp);
 /*
  *   returns "(sysdate+2.5)"
@@ Line 35: / Line 55: @@
 $conn->Execute("UPDATE TABLE SET dodate=$fld WHERE ID=$id");
+</code>
+===== Example Using SQL Server =====
+<code php>
+/*
+* Get Date -273.5 Hours in the past
+*/
+$fld = $db->offsetDate("-273.5/24");
+/*
+* Returns " SELECT FORMAT(DATEADD(hour ,-273.5,GETDATE()), 'yyyy-MM-dd')"
+*/
+</code>
+===== Example Generating A Date From a column Value ====
+In this example we would use the statement to return a date 7 days forward from the current value of the column "delivery date".
+<code php>
+$newDeliveryDate = $db->offsetDate(7,'delivery_date');
+$SQL = "SELECT $newDeliveryDate
+          FROM delivery_master
+         WHERE delivery_date = {$db->dbDate(date('Y-m-d')}".
 </code>