Geek Answers Handbook

SQL Server 2008 Time Zone Conversion

In my web application, all date values ​​are converted to UTC before being transferred to the SQL database, and then converted back to local time when they are displayed in the application. Therefore, the bottom line indicates that all dates are in UTC in the database.

This worked fine until I started developing the report. In Crystal Reports there is no way (I can detect) to convert the time zone, so I need to do the conversion to SQL before passing the value to the report.

SQL Server seems to have a much more limited ability to convert date and time values. The only function I see is SWITCHOFFSET. The problem with this function is that it does not know about daylight, which, apparently, is the main disadvantage. In asp.net, I can pass a datetime value with a time zone, and it will automatically convert it to UTC, taking into account any necessary daylight saving settings.

In SQL though, instead of saying something like

SWITCHOFFSET (SomeDate,"Eastern Time"),

Now i have to say

 SWITCHOFFSET(SomeDate, "-4:00").

But what happens if I pull out the data from the table and I ask for all the data for March, the month when the daylight saving time starts? Regardless, some of them will be wrong. I can say: -4: 00 or -5: 00, but obviously not both.

Is there any other function for this, since, frankly, it seems that SWITCHOFFSET is half-baked if it does not know how to make daylight saving.

+4
source share
3 answers

The only way I can think of is:

  • Use C # integration to create a method for this;) Thank God, you can come back to this.

  • Use VIEWS to launch reporting against them. ALWAYS a good idea, because it separates reports (outside of your direct control) from the database schema, which is an implementation detail. Usually I have a SCHEMA report that has all the reporting views as another documented API.

Last but not least, you MAY try to get rid of SQL Access and use the OData API, but this is a definite job.

In any case, views can use the method you programmed to convert time.

+1
source

For those using SSRS reports, you can find TimeZone in SSRS. Refer to the post here for more information.

Working with time zones in SSRS

0
source

It looks like your request will be granted

TIME CLOCK TIME

In SQL 2016, anyway

-1
source

More articles:


All Articles