I'm converting a DB from mysql/linux to sql server/windows and I found that converting timezones is not as easy as it was in mysql/linux. In mysql/linux you can call convert_tz(time,orgTimeZone,newTimezone) as a function in the query but no such luck in sql server/windows. If you don't want to generate your own timezone tables you can let .net and c# do the legwork for you and just convert the time on the front end (the WAS). I store all of my times in UTC and this function has come in handy. MS timezone names can be found here: http://technet.microsoft.com/en-us/l...(v=ws.10).aspx
To convert timezones in a grid, just run the function in the "display format" and in the search part set the server side "can search" event e.SearchDataSubmitted.timestamp (or whatever your field is called) to the result of this function.
To convert timezones in a grid, just run the function in the "display format" and in the search part set the server side "can search" event e.SearchDataSubmitted.timestamp (or whatever your field is called) to the result of this function.
Code:
FUNCTION convert_tz_utc AS T (baseTime as T, timeZone as C, fromUTC as L) ' Copyright 2012 Jason Beneteau ' ' Licensed under the Apache License, Version 2.0 (the "License"); ' you may not use this file except in compliance with the License. ' You may obtain a copy of the License at ' ' http://www.apache.org/licenses/LICENSE-2.0 ' ' Unless required by applicable law or agreed to in writing, software ' distributed under the License is distributed on an "AS IS" BASIS, ' WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. ' See the License for the specific language governing permissions and ' limitations under the License. ' Purpose: Use 1 function to convert to/from UTC and any given timezone ' Last Modified: 10/02/2012 ' Last Modified By: Jason Beneteau 'If baseTime is null pass back null to avoid errors if baseTime = null_value() then convert_tz_utc = null_value() end end if 'Setup the timezone name pointer DIM zoneID as P zoneID = ::System::TimeZoneInfo::FindSystemTimeZoneById(timeZone) 'Convert to or from UTC if fromUTC then convert_tz_utc = ::System::TimeZoneInfo::ConvertTimeFromUtc(baseTime,zoneID) else convert_tz_utc = ::System::TimeZoneInfo::ConvertTimeToUtc(baseTime,zoneID) end if END FUNCTION