Differences

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

--- v5:reference:connection:offsetdate [2025/08/20 03:03] – fix and describe second arg mnewnham
+++ v5:reference:connection:offsetdate [2025/08/23 18:21] (current) – Move note into an Info wrap dregad
@@ Line 4: / Line 4: @@
 == Syntax ==
   string|bool ADOConnection::offsetDate(
-         string|float $offset
+         float $offset
          optional string $isoDate
   )
 </WRAP>
 ===== 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.
@@ Line 14: / Line 15: @@
 The function returns false if invalid parameters are provided.
+<WRAP info>
+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
+</WRAP>
 ===== Inputs =====
-The $offset parameter can be presented in 2 ways:
+The $offset is the number of days that should be added or subtracted from the reference date; the decimal portion is the fraction of a day (e.g. 0.5 = 12 hours)
-  * 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, or skipped or false for today
+The $isoDate can be:
-===== Example =====
+   * A basis date in ISO format including a time portion, if a fractional calculation is required
-In Oracle, to find the date and time that is 2.5 days from today, you can use:
+   * An SQL statement that produces an ISO date
-<code php>
+   * An SQL column if the method is to be used to calculate date offsets based on table values
-/*
+   * skipped or false for today
- * get date one week from now
- */
+===== Examples =====
+Get date one week from now
+<code php>
 $fld = $conn->OffsetDate(7);
 /*
  * returns "(trunc(sysdate)+7")
  */
+</code>
-/*===
+Oracle: get date and time that is 60 hours (2.5 days) from current
- *  get date and time that is 60 hours from current date and time
- */
-$fld = $conn->OffsetDate("60/24", $conn->sysTimeStamp);
+<code php>
+$fld = $conn->OffsetDate(60/24, $conn->sysTimeStamp);
 /*
  *   returns "(sysdate+2.5)"
@@ Line 45: / Line 54: @@
 </code>
-===== Example Using SQL Server =====
+SQL Server: Get Date -273.5 Hours in the past
 <code php>
+$fld = $db->offsetDate(-273.5/24);
 /*
-* Get Date -273.5 Hours in the past
+ * Returns " SELECT FORMAT(DATEADD(hour ,-273.5,GETDATE()), 'yyyy-MM-dd')"
-*/
+ */
-$fld = $db->offsetDate("-273.5/24");
+</code>
-/*
+Generating A Date From a column Value.
-* Returns " SELECT FORMAT(DATEADD(hour ,-273.5,GETDATE()), 'yyyy-MM-dd')"
+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";
 </code>