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/23 18:21] (current) – Move note into an Info wrap dregad
@@ 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
+         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:
+===== Description =====
-<code php>
+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.
-/*
- * get date one week from now
- */
+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.
+<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 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)
+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
+===== 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(2.5, $conn->sysTimeStamp);
+<code php>
+$fld = $conn->OffsetDate(60/24, $conn->sysTimeStamp);
 /*
  *   returns "(sysdate+2.5)"
@@ Line 35: / Line 52: @@
 $conn->Execute("UPDATE TABLE SET dodate=$fld WHERE ID=$id");
+</code>
+SQL Server: Get Date -273.5 Hours in the past
+<code php>
+$fld = $db->offsetDate(-273.5/24);
+/*
+ * Returns " SELECT FORMAT(DATEADD(hour ,-273.5,GETDATE()), 'yyyy-MM-dd')"
+ */
+</code>
+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";
 </code>