SQL Server needs the mySql DATE_FORMAT function

Problem/Motivation

Enabling the built-in archive view results in the following error:

SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near ')'

This is because mySql has a built-in function to format dates called DATE_FORMAT that is missing in SQL Server. To further complicate things, SQL Server 2008 and earlier versions used the CONVERT functions to handle date formatting, but SQL Server 2012 has a new FORMAT function. See:
https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#fun... and
http://msdn.microsoft.com/en-us/library/hh213505(v=sql.110).aspx

Proposed resolution

Just like the install of the sqlsrv module creates Scalar-value functions CONCAT, GREATEST, IF, SUBSTRING, and SUBSTRING_INDEX, I propose that a new function called DATE_FORMAT be created that.

CREATE FUNCTION [dbo].[DATE_FORMAT] 
(
	-- Add the parameters for the function here
	@Datetime DATETIME,
	@FormatMask VARCHAR(32)
)
RETURNS VARCHAR(32)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Result VARCHAR(32)

	-- Add the T-SQL statements to compute the return value here
	DECLARE @StringDate VARCHAR(32)

    SET @StringDate = @FormatMask
    
    IF (CHARINDEX ('YYYY',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'YYYY',
                         DATENAME(YY, @Datetime))

    IF (CHARINDEX ('yy',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'yy',
                         RIGHT(DATENAME(YY, @Datetime),2))

    IF (CHARINDEX ('MMMM',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'MMMM',
                         DATENAME(MM, @Datetime))

    IF (CHARINDEX ('MMM',@StringDate)>0)
       SET @StringDate = REPLACE(@StringDate, 'MMM',
                         LEFT(UPPER(DATENAME(MM, @Datetime)),3))

    IF (CHARINDEX ('MM',@StringDate COLLATE Latin1_General_CS_AS) > 0)
       SET @StringDate = REPLACE(@StringDate COLLATE Latin1_General_CS_AS, 'MM',
                  RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))

    IF (CHARINDEX ('M',@StringDate COLLATE Latin1_General_CS_AS) > 0)
       SET @StringDate = REPLACE(@StringDate COLLATE Latin1_General_CS_AS, 'M',
                         CONVERT(VARCHAR,DATEPART(MM, @Datetime)))

    IF (CHARINDEX ('DDDD',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'DDDD',
                                     DATENAME(DW, @Datetime))   
    IF (CHARINDEX ('DDD',@StringDate) > 0)
       SET @StringDate = LEFT(REPLACE(@StringDate, 'DDD',
                                     DATENAME(DW, @Datetime)),3)
    IF (CHARINDEX ('dd',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'dd',
                         RIGHT('0'+DATENAME(DD, @Datetime),2))

    IF (CHARINDEX ('d',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'd',
                         RIGHT(DATENAME(DD, @Datetime),2))
                                     
    IF (CHARINDEX ('ww',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'ww',
                                     DATENAME(WW, @Datetime))   
                                     
    IF (CHARINDEX ('HH',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'HH',
                                     DATENAME(HH, @Datetime))  
       --IF @StringDate > 12 SET @StringDate = @StringDate - 12

    IF (CHARINDEX ('mm',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'mm',
                         RIGHT('0'+DATENAME(MI, @Datetime),2)) 

    IF (CHARINDEX ('ss',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'ss',
                         RIGHT('0'+DATENAME(SS, @Datetime),2))
	
	SELECT @Result = @StringDate

	-- Return the result of the function
	RETURN @Result

END

Remaining tasks

Need to decide if the DATE_FORMAT function should be forward-looking to match the format strings of SQL 2012 or look towards mySql and use its format strings (e.g., 'YYYY' or '%Y').

The installation of sqlsrv could determine if the user is already on SQL Server 2012... what to do then, since the FORMAT function is available? Maybe then the DATE_FORMAT function would simply call FORMAT?

There are also other modules with outstanding issues on drupal.org that require dates to be formatted for sqlsrv.

User interface changes

None.

API changes

The views/includes/handlers.inc file needs to be updated to account for sqlsrv. Example of a patch follows:

--- C:/Users/r_johnston/Downloads/views/includes/handlers.inc	Tue Feb 19 21:56:40 2013
+++ C:/inetpub/wwwroot/drupal_wet/profiles/wetkit/modules/contrib/views/includes/handlers.inc	Mon Feb 25 15:48:57 2013
@@ -1261,6 +1261,18 @@
         $field = "($field + '$offset_seconds')";
       }
       return $field;
+    case 'sqlsrv':
+      switch ($field_type) {
+        case 'int':
+          $field = "DATEADD(second, $field, '1970-01-01')";
+          break;
+        case 'datetime':
+          break;
+      }
+       if (!empty($offset)) {
+         $field = "DATEADD(second, $offset_seconds, $field)";
+       }
+      return $field;
   }
 }
 
@@ -1347,6 +1359,28 @@
       );
       $format = strtr($format, $replace);
       return "strftime('$format', $field, 'unixepoch')";
+    case 'sqlsrv':
+      $replace = array(
+        'Y' => 'YYYY',  // 4 digit year number
+        'y' => 'yy',    // 2 digit year number
+        'M' => 'MMM',   // 3 letter month name
+        'm' => 'MM',    // month number with leading zeros
+        'n' => 'M',     // month number without leading zeros
+        'F' => 'MMMM',  // full month name
+        'D' => 'ddd',   // 3 letter day name
+        'd' => 'dd',    // day of month number with leading zeros
+        'l' => 'dddd',  // full day name
+        'j' => 'd',     // day of the month without leading zeros
+        'W' => 'ww',    // ISO week number (not in SQL2012 Format function?)
+        'H' => 'HH',    // 24 hour hour with leading zeros
+        'h' => 'hh',    // No format for 12 hour hour with leading zeros
+        'i' => 'mm',    // minutes with leading zeros
+        's' => 'ss',    // seconds with leading zeros
+        'A' => '',      // No format for the AM/PM designator.
+      );
+      $format = strtr($format, $replace);
+      //REVIEW:  Make the DATE_FORMAT function a mirror of the MySql function or the SQL2012 FORMAT function?
+      return "dbo.DATE_FORMAT($field, '$format')";
   }
 }
 
@@ -1376,11 +1410,29 @@
   case('DATE'):
     return $field;
   case('YEAR'):
-    return "EXTRACT(YEAR FROM($field))";
+    switch ($db_type) {
+        case('sqlsrv'):
+          return "YEAR($field)";
+          break;
+        default:
+          return "EXTRACT(YEAR FROM($field))";
+      }
   case('MONTH'):
-    return "EXTRACT(MONTH FROM($field))";
+    switch ($db_type) {
+      case('sqlsrv'):
+        return "MONTH($field)";
+        break;
+      default:
+        return "EXTRACT(MONTH FROM($field))";
+    }
   case('DAY'):
-    return "EXTRACT(DAY FROM($field))";
+    switch ($db_type) {
+      case('sqlsrv'):
+        return "DAY($field)";
+        break;
+      default:
+        return "EXTRACT(DAY FROM($field))";
+    }
   case('HOUR'):
     return "EXTRACT(HOUR FROM($field))";
   case('MINUTE'):
@@ -1394,6 +1446,8 @@
         return "WEEK($field, 3)";
       case('pgsql'):
         return "EXTRACT(WEEK FROM($field))";
+      case('sqlsrv'):
+        return "DATEPART(week, $field)";
     }
   case('DOW'):
     switch ($db_type) {
@@ -1403,6 +1457,8 @@
         return "INTEGER(DAYOFWEEK($field) - 1)";
       case('pgsql'):
         return "EXTRACT(DOW FROM($field))";
+      case('sqlsrv'):
+        return "DATEPART(weekday, $field) - 1";
     }
   case('DOY'):
     switch ($db_type) {
@@ -1410,6 +1466,8 @@
         return "DAYOFYEAR($field)";
       case('pgsql'):
         return "EXTRACT(DOY FROM($field))";
+      case('sqlsrv'):
+        return "DATEPART(dayofyear, $field)";
     }
   }
 }