set_db_timezone in SQLSERVER

Someone will have to supply a patch. I only have the bare bones of a beginning of support for SQLSERVER. It won't get fleshed out without patches.

I just experienced the same problem, when I use a Date field as filter in a view. I tried twice, and afterwards I can't access my view without getting the error.

Same error to me, trying to edit some views.

Date dev2011-09-08, Views dev2011-10-07, Drupal 7.2, Sqlsrv 2011-05-31.

Following

The offending php routine is at date_api_sql.inc:

/**
* Set the database timzone offset.
*
* Setting the db timezone to UTC is done to ensure consistency in date
* handling whether or not the database can do proper timezone conversion.
*
* Views filters that not exposed are cached and won't set the timezone
* so views date filters should add 'cacheable' => 'no' to their
* definitions to ensure that the database timezone gets set properly
* when the query is executed.
*
* @param $offset
* An offset value to set the database timezone to. This will only
* set a fixed offset, not a timezone, so any value other than
* '+00:00' should be used with caution.
*/
function set_db_timezone($offset = '+00:00') {
static $already_set = FALSE;
$type = db_driver();
if (!$already_set) {
switch ($type) {
case 'mysql':
case 'mysqli':
db_query("SET @@session.time_zone = '$offset'");
break;
case 'pgsql':
db_query("SET TIME ZONE INTERVAL '$offset' HOUR TO MINUTE");
break;
case 'sqlsrv':
db_query('TimeZone.setDefault(TimeZone.getTimeZone("GMT"))');
break;
}
$already_set = TRUE;
}
}

It seems that the code for the SQLSRV case is pending, and it is written the JDBC syntax instead of the correct SQL sentence.

In the meanwhile I can bypass the error and edit the view if I comment that line:

/////// next line commented so we can edit the view instead of throwing SQL error
/////// db_query('TimeZone.setDefault(TimeZone.getTimeZone("GMT"))');

The definitive solution would be to write the correct query to set the time zone in SQLServer.

As I described here, this is a serious, difficult to diagnose problem with severe site impacts. One may no longer add new content types or edit the fields of existing ones:
http://drupal.org/node/1263802#comment-5223836

As the previous comment shows, commenting out that single line serves as a workaround for SQLSRV users.

I commented that line out in the code. Leaving this issue open for someone to provide a proper patch.

Flagging non-MYSQL database issues.

I'm not sure that 'TimeZone.setDefault(TimeZone.getTimeZone("GMT"))' is MSSQL statement (it is more similar to Java JDBC driver). As I know, MSSQL doesn't allow setting default timezone.

Indeed, MSSQL does not allow any way to set a default timezone. That's because it only allows one default and that is the localtime offset set in the machine's system registry. If you want to save times as UTC, you have three choices:

1. Adjust times to UTC by hand with functions like DATEADD().

2. Store all times as datetimeoffsets, so both the time and the offset from UTC is stored. This uses 10 bytes of storage instead of 8 bytes for datetime or 4 bytes for smalldatetime and could impact database performance, but it's considered the official solution and offers the best support for accounting for DST with time zone offset conversion functions.

3. Set your system's timezone to UTC and use UTC for everything. Learn to speak with a Greenwich accent and live on different day-night cycles.

In all three cases, MSSQL offers no automatic accounting for DST when depicting future or historical times.

Not 'major' because it only affects one type of database and I've explicitly already said that support for that database is likely to be broken. I don't want an issue like this to block a release of everything else, especially since it will only get fixed if someone using that database provides a patch.