Differences

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

--- v5:reference:connection:offsetdate [2025/07/12 03:38] – mnewnham
+++ v5:reference:connection:offsetdate [2025/08/20 05:04] (current) – mnewnham
@@ Line 3: / Line 3: @@
 <WRAP right box>
 == Syntax ==
-  string offsetDate(
+  string|bool ADOConnection::offsetDate(
-         string $offset
+         string|float $offset
-         optional float $baseDate
+         optional string $isoDate
   )
 </WRAP>
+==== 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 ''$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()''. The value returned when the statement is execute is an //**ISO Date**//
+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 =====
@@ Line 15: / Line 26: @@
   * 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:
@@ Line 27: / Line 45: @@
  */
-/*
+/*===
  *  get date and time that is 60 hours from current date and time
  */
@@ Line 37: / 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>