public function date_sql_handler::sql_format in Date 7.2

Same name and namespace in other branches

1. 5.2 date_api_sql.inc \date_sql_handler::sql_format()
2. 6.2 date_api_sql.inc \date_sql_handler::sql_format()
3. 6 date_api_sql.inc \date_sql_handler::sql_format()
4. 7.3 date_api/date_api_sql.inc \date_sql_handler::sql_format()
5. 7 date_api/date_api_sql.inc \date_sql_handler::sql_format()

Helper function to create cross-database SQL date formatting.

Parameters

string $format: A format string for the result, like 'Y-m-d H:i:s' .

string $field: The real table and field name, like 'tablename.fieldname' .

Return value

string An appropriate SQL string for the db type and field type.

1 call to date_sql_handler::sql_format()

date_sql_handler::sql_where_format in date_api/date_api_sql.inc

Create a where clause to compare a formated field to a formated value.

File

date_api/date_api_sql.inc, line 420

SQL helper for Date API.

Class

date_sql_handler

A class to manipulate date SQL.

Code

public function sql_format($format, $field) {
  switch ($this->db_type) {
    case 'mysql':
      $replace = array(
        'Y' => '%Y',
        'y' => '%y',
        'M' => '%b',
        'm' => '%m',
        'n' => '%c',
        'F' => '%M',
        'D' => '%a',
        'd' => '%d',
        'l' => '%W',
        'j' => '%e',
        'W' => '%v',
        'H' => '%H',
        'h' => '%h',
        'i' => '%i',
        's' => '%s',
        'A' => '%p',
        '\\WW' => 'W%U',
      );
      $format = strtr($format, $replace);
      return "DATE_FORMAT({$field}, '{$format}')";
    case 'pgsql':
      $replace = array(
        'Y' => 'YYYY',
        'y' => 'YY',
        'M' => 'Mon',
        'm' => 'MM',
        // No format for Numeric representation of a month, without leading
        // zeros.
        'n' => 'MM',
        'F' => 'Month',
        'D' => 'Dy',
        'd' => 'DD',
        'l' => 'Day',
        // No format for Day of the month without leading zeros.
        'j' => 'DD',
        'W' => 'WW',
        'H' => 'HH24',
        'h' => 'HH12',
        'i' => 'MI',
        's' => 'SS',
        'A' => 'AM',
        '\\T' => '"T"',
      );
      $format = strtr($format, $replace);
      return "TO_CHAR({$field}, '{$format}')";
    case 'sqlite':
      $replace = array(
        // 4 digit year number.
        'Y' => '%Y',
        // No format for 2 digit year number.
        'y' => '%Y',
        // No format for 3 letter month name.
        'M' => '%m',
        // Month number with leading zeros.
        'm' => '%m',
        // No format for month number without leading zeros.
        'n' => '%m',
        // No format for full month name.
        'F' => '%m',
        // No format for 3 letter day name.
        'D' => '%d',
        // Day of month number with leading zeros.
        'd' => '%d',
        // No format for full day name.
        'l' => '%d',
        // No format for day of month number without leading zeros.
        'j' => '%d',
        // ISO week number.
        'W' => '%W',
        // 24 hour hour with leading zeros.
        'H' => '%H',
        // No format for 12 hour hour with leading zeros.
        'h' => '%H',
        // Minutes with leading zeros.
        'i' => '%M',
        // Seconds with leading zeros.
        's' => '%S',
        // No format for AM/PM.
        'A' => '',
        // Week number.
        '\\WW' => '',
      );
      $format = strtr($format, $replace);
      return "strftime('{$format}', {$field})";
    case 'sqlsrv':
      $replace = array(
        // 4 digit year number.
        'Y' => "' + CAST(DATEPART(year, {$field}) AS nvarchar) + '",
        // 2 digit year number.
        'y' => "' + RIGHT(DATEPART(year, {$field}), 2) + '",
        // 3 letter month name.
        'M' => "' + LEFT(DATENAME(month, {$field}), 3) + '",
        // Month number with leading zeros.
        'm' => "' + RIGHT('0' + CAST(DATEPART(month, {$field}) AS nvarchar), 2) + '",
        // Month number without leading zeros.
        'n' => "' + CAST(DATEPART(month, {$field}) AS nvarchar) + '",
        // Full month name.
        'F' => "' + DATENAME(month, {$field}) + '",
        // 3 letter day name.
        'D' => "' + LEFT(DATENAME(day, {$field}), 3) + '",
        // Day of month number with leading zeros.
        'd' => "' + RIGHT('0' + CAST(DATEPART(day, {$field}) AS nvarchar), 2) + '",
        // Full day name.
        'l' => "' + DATENAME(day, {$field}) + '",
        // Day of month number without leading zeros.
        'j' => "' + CAST(DATEPART(day, {$field}) AS nvarchar) + '",
        // ISO week number.
        'W' => "' + CAST(DATEPART(iso_week, {$field}) AS nvarchar) + '",
        // 24 hour with leading zeros.
        'H' => "' + RIGHT('0' + CAST(DATEPART(hour, {$field}) AS nvarchar), 2) + '",
        // 12 hour with leading zeros.
        // Conversion to 'mon dd yyyy hh:miAM/PM' format (corresponds to style
        // 100 in MSSQL).
        // Hour position is fixed, so we use SUBSTRING to extract it.
        'h' => "' + RIGHT('0' + LTRIM(SUBSTRING(CONVERT(nvarchar, {$field}, 100), 13, 2)), 2) + '",
        // Minutes with leading zeros.
        'i' => "' + RIGHT('0' + CAST(DATEPART(minute, {$field}) AS nvarchar), 2) + '",
        // Seconds with leading zeros.
        's' => "' + RIGHT('0' + CAST(DATEPART(second, {$field}) AS nvarchar), 2) + '",
        // AM/PM.
        // Conversion to 'mon dd yyyy hh:miAM/PM' format (corresponds to style
        // 100 in MSSQL).
        'A' => "' + RIGHT(CONVERT(nvarchar, {$field}, 100), 2) + '",
        // Week number.
        '\\WW' => "' + CAST(DATEPART(week, {$field}) AS nvarchar) + '",
        '\\T' => 'T',
        // MS SQL uses single quote as escape symbol.
        '\'' => '\'\'',
      );
      $format = strtr($format, $replace);
      $format = "'{$format}'";
      return $format;
  }
}