This is an old revision of the document!

offsetDate

Syntax

string|bool ADOConnection::offsetDate( 
       string|float $offset
       optional string $isoDate
)

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:

/*
 * get date one week from now  
 */
 
$fld = $conn->OffsetDate(7); 
/*
 * returns "(trunc(sysdate)+7")
 */
 
/*===
 *  get date and time that is 60 hours from current date and time 
 */ 
 
$fld = $conn->OffsetDate("60/24", $conn->sysTimeStamp); 
/*
 *   returns "(sysdate+2.5)"
 */  
 
$conn->Execute("UPDATE TABLE SET dodate=$fld WHERE ID=$id");

Example Using SQL Server

/*
* 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')"
*/

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”.

$newDeliveryDate = $db->offsetDate(7,'delivery_date');
 
$SQL = "SELECT $newDeliveryDate 
          FROM delivery_master 
         WHERE delivery_date = {$db->dbDate(date('Y-m-d')}".